Numbering Rows with a Twist

OK,

Here is my challenge.

If I have a query that produces the following

ItemSold_On
A01-10-2004 8:03
A01-11-2004 10:05
A01-12-20041:37
A01-14-20047:16
B01-10-20049:37
B01-12-2004 11:42
B01-13-20049:37

But I need it to produce this instead

ItemSold_On Instance
A01-10-2004 8:031
A01-11-2004 10:052
A01-12-20041:373
A01-14-20047:164
B01-10-20049:371
B01-12-2004 11:422
B01-13-20049:373

So basically I need it to chronologically number the rows, but I need
the count to start over when the item changes.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
--|||> But I need it to produce this instead
> ItemSold_On Instance
> A01-10-2004 8:031
> A01-11-2004 10:052
> A01-12-20041:373
> A01-14-20047:164
> B01-10-20049:371
> B01-12-2004 11:422
> B01-13-20049:373

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;

Christian.|||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 ***
Don't just participate in USENET...get rewarded for it!