sql server (alternate): OK,

Here is my challenge.

If I have a query that produces the following

Item Sold_On
A 01-10-2004 8:03
A 01-11-2004 10:05
A 01-12-2004 1:37
A 01-14-2004 7:16
B 01-10-2004 9:37
B 01-12-2004 11:42
B 01-13-2004 9:37

But I need it to produce this instead

Item Sold_On Instance
A 01-10-2004 8:03 1
A 01-11-2004 10:05 2
A 01-12-2004 1:37 3
A 01-14-2004 7:16 4
B 01-10-2004 9:37 1
B 01-12-2004 11:42 2
B 01-13-2004 9:37 3

So basically I need it to chronologically number the rows, but I need

[quoted text, click to view]

This won't work until Yukon, but already works in Oracle and DB2...

select item
, sold_on
, row_number() over(partition by item
order by sold_on) as Instance
from your_table;

You haven't given us any information about your base table(s). I'll assume
the query result you posted represents an actual table that looks like this:

CREATE TABLE Sometable (item CHAR(1), sold_on DATETIME, PRIMARY KEY
(item,sold_on))

INSERT INTO Sometable VALUES ('A', '2004-01-10T08:03:00')
INSERT INTO Sometable VALUES ('A', '2004-01-11T10:05:00')
INSERT INTO Sometable VALUES ('A', '2004-01-12T01:37:00')
INSERT INTO Sometable VALUES ('A', '2004-01-14T07:16:00')
INSERT INTO Sometable VALUES ('B', '2004-01-10T09:37:00')
INSERT INTO Sometable VALUES ('B', '2004-01-12T11:42:00')
INSERT INTO Sometable VALUES ('B', '2004-01-13T09:37:00')

Here's the query. Depending on your actual data this may not work as
expected (it relies on the combination of (item, sold_on) being unique) or
there may be a better way.

SELECT S1.item, S1.sold_on, COUNT(*) AS instance
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.item = S2.item
AND S1.sold_on >= S2.sold_on
GROUP BY S1.item, S1.sold_on

Hope this helps.

--
David Portas
------------
Please reply only to the newsgroup
--

Thanks David,

I will give it a shot.

Actually the recordset you saw would be from a previous query but I at
least know enough to get your suggestion to work. Even if I have to
create a logical table to do it.


Thanks again,

David Meriwether


*** Sent via Developersdex http://www.developersdex.com ***