Ryan Sabir wrote:
>
> I have a database that looks something like this:
>
> Record ID YEAR Name
> 1 2001 Some name1
> 2 2001 Some name2
> 3 2002 Some name3
> 4 2002 Some name4
> 5 2002 Some name5
> 6 2002 Some name6
> 7 2002 Some name7
> 8 2003 Some name8
> 9 2003 Some new name
> 10 2003 Some other name
>
> I want to generate a number for each of these records the shows what record
> of that particular year it was... imagine these are magazines and I want to
> generate an issue number that resets to 1 at the start of each year
>
> Record 1 would be issue number 1 of 2001
> Record 4 would be issue number 2 of 2002
> Record 8 would be issue number 1 of 2003
> Record 10 would be issue number 3 of 2003
SELECT
RecordID,
Year,
(
SELECT Count(1) + 1
FROM table b
WHERE b.Year = a.Year AND b.RecordID < a.RecordID
) AS Issue
FROM test a
Depending on the number of records/indexes the followig could be faster:
SELECT
a.RecordID,
a.Year,
Count(b.RecordID) AS Issue
FROM
table a INNER JOIN table b
ON (a.Year = b.Year AND b.RecordID <= a.RecordID)
GROUP BY
a.RecordID,
a.Year
Jochem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
