Search code examples
sqlsql-server-2000

Serial No Based On Column Info Required In SQL

I have few columns in my table (SQL Server 2000 database). I have information like

OrderID      Design        Qty
------------------------------    
28500   R6192WHM+DWN    1
28500   P3150WHM+DWN    1
28504   E3085/2DWN  1
28503   R5595HCT(P) 1
28503   R4768DWN    1
28505   E3415D(P)   1
28505   E3413DWN    1
28505   E3365AQ(P)  1

I require information like this (i.e sno based on orderid )

OrderID   Design        Qty    SNO
-------------------------------------    
28500   R6192WHM+DWN    1       1
28500   P3150WHM+DWN    1       2
28504   E3085/2DWN  1       1
28503   R5595HCT(P) 1       1
28503   R4768DWN    1       2 
28505   E3415D(P)   1       1
28505   E3413DWN    1       2
28505   E3365AQ(P)  1       3
Solution

  • Try this 

    select t1.*, SNO = count(*)
  from Table1 t1
  join Table1 t2
    on t1.OrderID = t2.OrderID
   and t1.Design >= t2.Design
 group by t1.OrderID, t1.Design, t1.Qty
 order by t1.OrderID, t1.Design