order by asc for two (2) columns
a co-worker today asked if there was a way to insert data from one table into another but sort the columns by ASC for BOTH columns. For example, tableA has the following value:
rowid: 1,2,3,4
columnA: 3,7,1,6
columnB: 6,7,2,4
In his new table, he wants it to look like so (tableB):
rowid: 1,2,3,4
columnA: 1,3,6,7
columnB: 2,4,6,7
@ first, it should be easy right?
insert into tableB(columnA, columnB)
select columnA, columnB from tableA order by columnA, columnB
But that will only sort by columnA in asc. ColumnB will not be sorted at all. Instead I ended up doing:
insert into tableB(columnA)
select columnA from tableA order by columnA
The above statement gives me the 1st column in ASC order, now for the 2nd:
update v1 set v1.columnB=v.columnB from
(select ROW_NUMBER() OVER (order by columnB) as RowNumber, columnB
from tableA ) v
inner join tableB v1 on v.RowNumber=v1.Rowid