Ryan, Do you work with stored procedures? If so, I might be able to work something out for you.
- Can you send the actual db lay-out and data? If not, - Are there always the same number of records in one year? ----- Original Message ----- From: "Ryan Sabir" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Tuesday, February 04, 2003 1:58 PM Subject: [cfaussie] SQL grouping > > 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/
