SQL sort order based on multiple column values

Question

We have data sorted like the format below. The first column is our system numbering, the second column is a back end system numbering, and the third column signifies that the rows with a ParentCol value actually below to the matching row of Col1. (So item 20 has two additional parts, item 30 and item 50.)

Col1      Col2      ParentCol
10        100       NULL
20        200       NULL
30        201       20
40        300       NULL
50        202       20
10        400       NULL
...

This is just a small example - there can sometimes be hundreds of rows per order. The application isn't handling this situation well from the standpoint of viewing the data in the UI. Ideally, I would like to have the content sorted like this, with our ID number (Col1) being the primary sorting, but recognizing that the back-end ID number (Col2) should immediately follow when there is a ParentCol value matching our ID:

Col1      Col2      ParentCol
10        100       NULL
10        400       NULL
20        200       NULL
30        201       20
50        202       20
40        300       NULL

Using T-SQL, how would I write a query that returns the data as I need it? Because of the various numbering schemes and possible valid duplicate values in our numbering scheme, Col1, (but not the back-end column, Col2), I haven't been able to figure this one out.

Thank you,

Scott

Stuart Ainsworth · Accepted Answer · 2011-11-07 19:58:47Z

2

SELECT *
FROM @t 
ORDER BY CASE WHEN ParentID IS NULL THEN col1 ELSE ParentID END, col2

answered Nov 7, 2011 at 19:58

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

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

1 Comment

Andriy M Over a year ago

+1, also, using COALESCE instead of CASE: … ORDER BY COALESCE(ParentID, col1), col2.

Collectives™ on Stack Overflow

SQL sort order based on multiple column values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related