I have data in this order
Id Name ---- ------------ 6783 Football 6783 Football 4394 Football 4394 Football 1786 Cricket 1786 Cricket 3265 Tennis 3265 Tennis 2601 Hockey 2601 Hockey 9554 Badminton 9554 Badminton 1947 Basketball 1947 Basketball
How can I sort this data in this order?
Id Name ---- ------------ 6783 Football 4394 Football 1786 Cricket 3265 Tennis 2601 Hockey 9554 Badminton 1947 Basketball 6783 Football 4394 Football 1786 Cricket 3265 Tennis 2601 Hockey 9554 Badminton 1947 Basketball
Advertisement
Answer
To do this requires a big assumption which is that the original sequence is reproducible somehow. Just by entering those rows into a database without a sequence we would be unable to be guaranteed to reproduce the result.
This code assigns a sequence to the original data. Using the column o_seq the ORDER BY produces the requested order.
drop table if exists dbo.test_table;
go
create table dbo.test_table(
Id int not null,
[Name] varchar(100) not null);
insert dbo.test_table(Id, [Name]) values
(6783,'Football'),
(6783,'Football'),
(4394,'Football'),
(4394,'Football'),
(1786,'Cricket'),
(1786,'Cricket'),
(3265,'Tennis'),
(3265,'Tennis'),
(2601,'Hockey'),
(2601,'Hockey'),
(9554,'Badminton'),
(9554,'Badminton'),
(1947,'Basketball'),
(1947,'Basketball');
drop table if exists dbo.test_table2;
go
create table dbo.test_table2(
Id int not null,
[Name] varchar(100) not null,
o_seq int);
/* this is not recomended. I had to visually inspect the rows to make sure this worked */
insert dbo.test_table2 select t.*, row_number() over (order by (select null)) from dbo.test_table t
/* produces the correct sort order */
;with nums_cte as (
select *, (o_seq-1)%2 s2
from dbo.test_table2)
select * from nums_cte
order by s2, o_seq;