CODEWITHSUNDEEP
sqlsql-server
Stefan Taseski
Stefan Taseski

Reputation: 232

SQL Sequence column with condition

I need to add new column to my SQL Query so i can sort the results afterwards by some ID (integer) and not by name (string).

The problem is I want to make sure that some of the names to be with highest SequenceID so i can put them at the end. I came up with something like this:

ROW_NUMBER() OVER(ORDER BY IIF([Name] = N'something', 'zzzzSomething', [Name]) ASC) AS SequenceID

Any better implementation?

Upvotes: 0

Views: 223

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Sort first by special name or not and then by name:

ROW_NUMBER() OVER (ORDER BY 
                    case name when N'John' then 3 when N'Mary' then 2 else 1 end, name
                  ) AS SequenceID

This sorts 'John' last, and before that 'Mary', and before that all other names in alphabetic order.

Upvotes: 1

Related Questions