CODEWITHSUNDEEP
sqlsql-order-by
Peak
Peak

Reputation: 21

insert sql records

I have a data like 

1
2
1
1
2
2
1
1
1
1
2
2
2
2

I need to order the data like 

1
2
1
2
1
2
1
2

the 3rd row "1" and the 5th row "2" should be ordered. Likewise 7th row "1" and the 10th row "2", 8th row "1" and the 11th row "2" etc.

In general, if the continuous count of "1" is 5, there will be 5 records with the value "2". 1->6,2->7,3->8 likewise it should be ordered.

Can you please tell me the logic in sql query? thanks in advance.

Upvotes: 0

Views: 51

Answers (2)

Serg
Serg

Reputation: 22811

If your dbms supports window functions you can order 1s and 2s this way

select col 
from tbl
order by 2*row_number() over(partition by col order by col) + case col when 1 then 0 else 1 end;

but there is no way to tell where was any row in the query result in the source, insert into tbl(col) values (1),(1),... statement for example.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

You can use row_number():

select t.*
from t
order by row_number() over (partition by col order by col);

If you have an ordering that you want between groups, then use that for the order by in the windowing clause.

Upvotes: 1

Related Questions