select year, (select count(*) from tablename t2 where t2.year = t.year and t2.recordid <= t.recordid)
from tablename t
nice puzzle, I enjoyed that
At 02:58 PM 2/4/2003 +1100, you wrote:
Hi all, 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 nameI 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 Now I can think of some simple CF ways to find this out, but what would be really elegant is to get SQL to work it out in the query, something like: <CFQUERY blah> SELECT recordID, year, name, funkyfunction(boink) AS issueNumber FROM myTable </CFQUERY> Anyone willing to have a try at this? There's a free cookie in it for you! (must be redeemed by visiting my web site). bye! ----------------------- Ryan Sabir Newgency Pty Ltd 2a Broughton St Paddington 2021 Sydney, Australia Ph (02) 9331 2133 Fax (02) 9331 5199 Mobile: 0411 512 454 http://www.newgency.com/index.cfm?referer=rysig --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
--- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
