I have data in this order
x
Id Name---- ------------6783 Football6783 Football4394 Football4394 Football1786 Cricket1786 Cricket3265 Tennis3265 Tennis2601 Hockey2601 Hockey9554 Badminton9554 Badminton1947 Basketball1947 BasketballHow can I sort this data in this order?
Id Name---- ------------6783 Football4394 Football1786 Cricket3265 Tennis2601 Hockey9554 Badminton1947 Basketball6783 Football4394 Football1786 Cricket3265 Tennis2601 Hockey9554 Badminton1947 BasketballAdvertisement
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;gocreate 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;gocreate 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_cteorder by s2, o_seq;