T-SQL: how to sort table rows based on 2 columns
- by Criss Nautilus
I'm quite stuck with this problem for sometime now..
How do I sort column A depending on the contents of Column B?
I have this sample:
ID count columnA ColumnB
12 1 A B
13 2 C D
14 3 B C
I want to sort it like this:
ID count ColumnA ColumnB
12 1 A B
14 3 B C
13 2 C D
so I need to sort the rows if the previous row of ColumnB = the next row of ColumnA
I'm thinking a loop? but can't quite imagine how it will work...
I was thinking it will go like this (maybe)
SELECT a.ID, a.ColumnA, a.ColumnB
FROM TableA WITH a (NOLOCK)
LEFT JOIN TableA b WITH (NOLOCK)
ON a.ID = b.ID
and a.counts = b.counts
Where a.columnB = b.ColumnA
the above code isn't working though and I was thinking more on the lines of...
DECLARE @counts int = 1
DECLARE @done int = 0
--WHILE @done = 0
BEGIN
SELECT a.ID, a.ColumnA, a.ColumnB
FROM TableA WITH a (NOLOCK)
LEFT JOIN TableA b WITH (NOLOCK)
ON a.ID = b.ID
and a.counts = @counts
Where a.columnB = b.ColumnA
set @count = @count +1
END
If this was a C code, would be easier for me but t-sql's syntax is making it a bit harder for a noobie like me.
