|
Subject:
|
How to insert row no automatically
|
|
Posted By:
|
Jane
|
Post Date:
|
1/12/2006 7:45:13 PM
|
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
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
1/13/2006 11:20:14 AM
|
-- 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
|
