You thought correctly Daniel. It works great! even in MS Access. Thanks heaps, I'll be sure to throw more fun puzzles your way in the future...
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Daniel Morphett Sent: Wednesday, 5 February 2003 9:34 AM To: CFAussie Mailing List Subject: [cfaussie] Re: SQL grouping this will work, I think 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 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 > >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/ --- 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/
