CODEWITHSUNDEEP
sqlsql-server-2000
Syed Muhammad Mubashir
Syed Muhammad Mubashir

Reputation: 1016

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

Upvotes: 1

Views: 184

Answers (2)

bvr
bvr

Reputation: 4826

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

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Try, which works in MS Sql sever 2005 and above:

select 
    *, 
    row_number() over (partition by OrderID order by OrderID) 
from YourTable

Sample

select 
    *, 
    row_number() over (partition by OrderID order by OrderID) 
from(
    select 28500 OrderID,   'R6192WHM+DWN' Design,  1 Qty union
    select 28500 OrderID,   'P3150WHM+DWN' Design,  1 Qty union
    select 28504 OrderID,   'E3085/2DWN' Design,    1 Qty union
    select 28503 OrderID,   'R5595HCT(P)' Design,   1 Qty union
    select 28503 OrderID,   'R4768DWN' Design,      1 Qty union
    select 28505 OrderID,   'E3415D(P)' Design,     1 Qty union
    select 28505 OrderID,   'E3413DWN' Design,      1 Qty union
    select 28505 OrderID,   'E3365AQ(P)' Design,    1 Qty
)x

Please try the below query, which works in sql server 2000 select *, (select count(*) from YourTable t2 where t2.OrderId=t1.OrderID and t2.Design<t1.Design)+1 as SNo From YourTable t1 order by OrderID, Design

Upvotes: 0

Related Questions