I need to organize a query based on a column in sequential order in a select distinct statement. The select distinct statement concatenates a character onto the front of a number (i.e “S1”, “S2″…”S11”) .
Here is the code:
x
select distinct
s.book_Id as EpbookId
,concat('S',ps.Param_Value) as bookID
,concat(sa.Location,'-',s.book_Number) as Label
,concat('<book ID="',concat('S',ps.Param_Value),
'" label="',concat(sa.Location,'-',s.book_Number),'"/>') as DataLine
from
books s
inner join books_Address sa on
s.book_Id = sa.book_Id
and sa.Addr_Type_Id = 1
and s.bookS_TYPE_ID = 1
and s.Active = 1
inner join Parameters_books ps on
ps.book_Id = s.book_Id
and ps.Param_Id = @bookParam
- Essentially I just need to order by
ps.Param_Value
- The problem is I can’t use a simple
ORDER BY ps.Param_Value
because I do not haveps.Param_Value
in my select distinct statement. - I also can not order by
bookID
because the result is not ordered correctly due to the letter in the front. - I also tried ordering by
SUBSTRING(bookID, 1, 10)
but again unless I putSUBSTRING(bookID, 1, 10)
in my select statement it will not work Due to it being a select distinct statement.
So is there a way to order by the numbers that follow the concatenated ‘S’ without adding something extra to my select statement. OR is it possible to add ps.Param_Value
to my select distinct statement without it actually coming back in my data?
Advertisement
Answer
I’m not sure I 100% follow, but I believe you could just:
- keep the ps.param_value in the distinct
- Add a select around the distinct block that computes the S + concat logic
- put an order by ps.param_value in the outer select
(you may want to explicitly list all columns, but I’ve omitted them for brevity here)
SELECT
*,
concat('S',ps.Param_Value) as bookID
FROM
(
select distinct
s.book_Id as EpbookId
,ps.Param_Value as Param_Value
,concat(sa.Location,'-',s.book_Number) as Label
,concat('<book ID="',concat('S',ps.Param_Value),
'" label="',concat(sa.Location,'-',s.book_Number),'"/>') as DataLine
from
books s
inner join books_Address sa on
s.book_Id = sa.book_Id
and sa.Addr_Type_Id = 1
and s.bookS_TYPE_ID = 1
and s.Active = 1
inner join Parameters_books ps on
ps.book_Id = s.book_Id
and ps.Param_Id = @bookPara
) A
ORDER BY
Param_Value