Hi,

i have a one table as this:


col1 col2

A 1000.00
A 3000.00
B 250.00
B 5000.00
B 1500.00
C 4000.00
C 2500.00
C 100.00
C 800.00
D 3500.00
D 8000.00


i need add one more column acccording to col2 in descending order as this:

col1 col2 seq_no
A 1000.00 2
A 3000.00 1
B 250.00 3
B 5000.00 1
B 1500.00 2
C 4000.00 1
C 2500.00 2
C 100.00 4
C 800.00 3
D 3500.00 2
D 8000.00 1



may i know what the SQL command if i need a seq_no as above?




Thanks

-- In SQL 2000 (and earlier) you need to use a correlated SubQuery
--Setup Test Data
CREATE TABLE dbo.OrderDetail
(OrderID int
,ProdID int
,Qty DECIMAL(12,4)
,PriceUtilized MONEY
,LineNum int);

INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (1,501,30,25.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (1,502,300,2.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (1,503,301,249.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (2,501,3,29.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (2,502,40,25.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (3,601,7,204.99);
INSERT dbo.OrderDetail (OrderID, ProdID, Qty,PriceUtilized)
VALUES (3,501,2000,25.99);

-- Update Statements Use Correlated SubQuery
UPDATE dbo.OrderDetail SET LineNum = (SELECT COUNT(*)
    FROM dbo.OrderDetail ODI
    WHERE ODI.OrderID = ORDERDetail.OrderID -- Limits us to the set belonging to one order
    AND ODI.PRODID >= ORDERDetail.ProdID -- Enables the Count to mimic Row_Number and
                                -- determines the order of the Line Nums
    )

SELECT ORDERID, PRODID, Qty, PriceUtilized, LineNum
FROM dbo.OrderDetail
Order BY OrderID, LineNum

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com