How to order by column minus first character in a select distinct statement

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:

    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

1. Essentially I just need to order by ps.Param_Value
2. The problem is I can’t use a simple ORDER BY ps.Param_Value because I do not have ps.Param_Value in my select distinct statement.
3. I also can not order by bookID because the result is not ordered correctly due to the letter in the front.
4. I also tried ordering by SUBSTRING(bookID, 1, 10) but again unless I put SUBSTRING(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?

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