How to sort multiple columns in SQL Server 2008 R2?

Question

There is a plain sort with order by clause with multiple columns separated by commas. But I am not looking for that. Please read below

The returned default result set is sorted by sequence column. Note sequence 46 and 47 which are in order with different ReqTag and Rank. I am trying to keep the ReqTag column together but sorted by sequence and Rank.

sequence | Reqtag | Rank
-------- | ------ | -------
42       | PD     | 1
43       | PD     | 2
46       | MQ     | 1
46       | SD     | 3
47       | MQ     | 2
47       | SD     | 4
48       | MO     | 1

Now, I want the display to be like below where sequence and rank column are used (desired result set)

sequence | Reqtag | Rank
-------- | ------ | ------
42       | PD     | 1
43       | PD     | 2
46       | MQ     | 1
47       | MQ     | 2
46       | SD     | 3
47       | SD     | 4
48       | MO     | 1

Any ideas are appreciated. Thank you for your time.

Gordon Linoff · Accepted Answer · 2017-01-22 14:02:04Z

1

You can use window functions in the sort. I think you want:

order by min(sequence) over (partition by reqtag),
         min(rank) over (partition by reqtag),
         rank

answered Jan 22, 2017 at 14:02

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guppies Over a year ago

Thank you Gordon. That worked. I am now trying to understand it.

Gordon Linoff Over a year ago

@Guppies . . . You can run the query with those columns added to the select. That should help you understand what the query is doing.

Teddy Sterne · Accepted Answer · 2017-01-22 14:08:16Z

1

If the order of the ReqTag is not important other than that they are grouped together then you should be able to change your sort to be the following:

SELECT sequence, ReqTag, Rank
FROM [Table]
ORDER BY ReqTag, sequence, Rank

answered Jan 22, 2017 at 14:08

Teddy Sterne

14.3k2 gold badges49 silver badges52 bronze badges

1 Comment

Dan Bracuk Over a year ago

I really don't understand how this answer does not meet the OP's requirements.

Collectives™ on Stack Overflow

How to sort multiple columns in SQL Server 2008 R2?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related