CODEWITHSUNDEEP
sqlsql-server-2012sql-order-by
Dimple
Dimple

Reputation: 57

SQL Query - Order by issue

When i have duplicate orders in table, the data displays like below. All header records first and then all line records for that particular order.

OrderNo     Column1  
300         Header   
300         Header   
300         Line   
300         Line    
200         Header  
200         Line  
200         Line

Expected output: 

OrderNo     Column1  
300         Header  
300         Line  
300         Header  
300         Line   
200         Header  
200         Line  
200         Line

I need to export this data in excel file. SO i need a proper sequence of records,i.e. Order1,Header,Line then Order2 Header,Line How can i achieve this using sql?

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You can interleave the headers and lines using row_number() in the order by clause:

select t.*
from t
order by orderno,
         row_number() over (partition by orderno, column1 order by ?);

The ? is for whatever column determines the final ordering among the rows. If you don't care, you can just use orderno.

Upvotes: 1

Related Questions