Set Row ID for Set of Like Values?

[mkelly11]

I need to create a numeric value (*EntryID) for each ID that is listed more than once in a table, for example:

ID ContractID EntryID
-- ---------- -------
12 1 1
12 1 2
12 1 3
12 2 1

So long as the ID and Contract are the same over several rows I need to add a "Counter" field...any suggestions are greatly appreciated.

 

[MartinCroft]

mkelly11

Interesting one, this has cursor written all over it, using a cursor would be easiest, if there is a set based solution it'll be interesting but if your not after a cute solution and look at cursors it gives you the option to compare the previous contractID and if the same just increment a variable and if different reset the variable again, writing these back to the table. Is this a static table in that once you have done the numbering no more rows will be added, if so you'll need to create an instead of trigger getting max(entry_id) for contractID enetered.

hope that given you a few ideas

if you need an idea how to construct the cursor let us know.

 

[mkelly11]

I have a cursor written, which I believe will work fine. But I'm having a case of the sometimers and just can't get the "Counter" to work correctly. Could you offer any suggestions on syntax based on the example data listed above?

 

[mrees]

I've seen something like this before on this forum (I would ignore mysticmart - Cursors are evil)

CREATE TABLE contracts (UniqueID int identity, [ID] int, contractID int)

INSERT INTO contracts ([ID],contractID) values (12,1)
INSERT INTO contracts ([ID],contractID) values (12,1)
INSERT INTO contracts ([ID],contractID) values (12,1)
INSERT INTO contracts ([ID],contractID) values (12,2)

SELECT [id],ContractID,(SELECT COUNT(*) FROM contracts c2 WHERE c1.[ID] = c2.[ID] and c1.ContractID = c2.contractID and c2.UniqueID <= c1.UniqueID)EntryID
FROM contracts c1

You could alter the code so that rather than having just the SELECT you UPDATE a table, using the uniqueID to determine which row to update.

Thanks must go to NigelRivett for this solution.

mrees

 

[simonsez]

I am trying to accomplish a version of this SQL statement.

I am trying to assign a row number (gameNum) that corrisponds to the Season and the grouping of that line item.
Season begins in August & ends in July
The grouping is the first three characters of the eventCode

So the first game in the season = 1
Game 2 would be next next line item in the season
...and so on.
Below is my syntax:

SELECT CASE WHEN month(a.event_datetime) >= 8 THEN YEAR(a.event_datetime) ELSE YEAR(a.event_datetime) + 1 END AS season, LEFT(a.eventCode, 3),
(SELECT COUNT(*) FROM events b
WHERE LEFT(a.eventCode, 3) = LEFT(b.eventCode, 3) AND (CASE WHEN month(a.event_datetime) >= 8 THEN YEAR(a.event_datetime)
ELSE YEAR(a.event_datetime) + 1 END) = (CASE WHEN month(b.event_datetime) >= 8 THEN YEAR(b.event_datetime)
ELSE YEAR(b.event_datetime) + 1 END) AND b.event_id <= event_id) gameNum
FROM events a
WHERE (a.eventCode LIKE 'efc%')

 

[simonsez]

...con't
The result is giving me the total count of line items for that season; the number appearing in every row.

What is wrong?

simonsez

 

[simonsez]

I figured it out by using the event_datetime instead of the event_id for the last condition....
Thanks anyway
simonsez