Skip to content
Advertisement

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:

  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?

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)

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement