Sorting By 2 Columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Sorting By 2 Columns

Author

Topic

JoeSzymkowicz
Starting Member

3 Posts

Posted - 2011-07-05 : 17:41:00

We have a table with IDs and sub-IDs. Some clients have subIDs . The table has 3 columns: Name, ID, and MasterID. MasterID is NULL if it is already a Master ID, as the column only denotes which sub-ID the Master IDs "contain".

Name ID MasterID
'Richard', '132', '130'
'Mike', '129', '130'
'Joe', '110', 'NULL'
'Meghan', '120', 'NULL'
'Dave', '130', 'NULL'
'Mike', '109', '110'
'Alan', '112', '110'
'Dale', '131', '130'

Ok so that is a small example of the table. Dave, Meghan and Joe have Master IDs. Joe has 3 sub-IDs under mine, displayed by them having my pin designated in their MasterID column.

I want the first row to be the numerically the first Master ID (record of an ID, with MasterID NULL). Under that, it should show any sub-IDs (in numeric order) of that Master ID. If there aren't any, it should so the next Master PIN and so on. So in this case, the table should look like this. Note that not sub-IDs are not necessarily always numerically larger values than their Master ID.

Name ID MasterID
'Joe', '110', 'NULL'
'Mike', '109', '110'
'Alan', '112', '110'
'Meghan', '120', 'NULL'
'Dave', '130', 'NULL'
'Mike', '129', '130'
'Dale', '131', '130'
'Richard', '132', '130'

I do not want to use a cursor for this as the table is fairly large and would take forever. I've looked into dynamic sorting with a CASE in the ORDER BY, but it sometimes puts the lower value sub-IDs above their Master IDs. Can anyone help?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-05 : 17:50:21

SELECT Name, ID, MasterID
FROM myTable
ORDER BY COALESCE(MasterID, ID), ID

JoeSzymkowicz
Starting Member

3 Posts

Posted - 2011-07-05 : 18:17:35

Using that query, it still places the sub-IDs with IDs lower than the Master ID ABOVE the Master ID in results. I am trying to have the sub-IDs, no matter what ID value they have, show up BELOW their respective Master ID.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 18:25:53

select Name, ID, MasterID
from tbl
order by coalesce(MasterID, ID), case when MasterID is null then 1 else 2 end, ID

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

JoeSzymkowicz
Starting Member

3 Posts

Posted - 2011-07-05 : 18:45:13

Wow, that did the trick. Thank you very much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources