Access 2000 Have got it working now but not very neatly.
Thanks for the help everyone. Giles Roadnight http://giles.roadnight.name -----Original Message----- From: Spike [mailto:[EMAIL PROTECTED]] Sent: 21 November 2002 18:09 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] Counting titles that do not start with letter using SQL Ah yes, Sorry mis-read the question... Did you mean to say you aren't on MSSQL? If not, which database are you using? Depending on the DB type, there may be a function that allows you to do a count based on the first letter. Also, if the DB supports sub-select statements, you should be able to do it. Spike Stephen Milligan Team Macromedia - ColdFusion Co-author 'Reality Macromedia ColdFusion MX: Intranets and Content Management' http://spikefu.blogspot.com > -----Original Message----- > From: Giles Roadnight > [mailto:[EMAIL PROTECTED]] > Sent: 21 November 2002 17:40 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Counting titles that do not start > with letter using SQL > > > Not quite what I want, don't have mysql either. > > Thanks anyway > > > Giles Roadnight > http://giles.roadnight.name > > > -----Original Message----- > From: Spike [mailto:[EMAIL PROTECTED]] > Sent: 21 November 2002 16:30 > To: [EMAIL PROTECTED] > Subject: RE: [ cf-dev ] Counting titles that do not start > with letter using SQL > > SELECT colname > FROM tablename > WHERE colname LIKE ('[^A]%') > > That will match anything except an 'A'. > > Spike > > Stephen Milligan > Team Macromedia - ColdFusion > Co-author 'Reality Macromedia ColdFusion MX: Intranets and > Content Management' http://spikefu.blogspot.com > > > -----Original Message----- > > From: Giles Roadnight > > [mailto:[EMAIL PROTECTED]] > > Sent: 21 November 2002 17:17 > > To: [EMAIL PROTECTED] > > Subject: [ cf-dev ] Counting titles that do not start with > > letter using SQL > > > > > > I am looping through a query of titles and I want to output > > the results per letter in two columns so under A there would > > be two columns of titles starting with A. To do this I need > > the count of records beginning with A which I have done like this: > > > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > select count(gameid) as lettercount > > from qry_getgames > > where name like '#request.currentLetter#%' > > </cfquery> > > > > How though do I get the count of titles that do not start > > with a letter? > > > > I could do it like this: > > <cfquery name="qry_getCurrentLettercount" dbtype="query"> > > select count(gameid) as lettercount > > from qry_getgames > > where name not like 'A%' > > and name not like 'B%' > > ect... > > </cfquery> > > But I don't want to have to write that out 52 times (upper and > > lowercase) > > > > Also is there a way to make SQL non case sensitive so the > > first query will count upper and lower case? > > > > Thanks > > > > Giles Roadnight > > http://giles.roadnight.name > > > > > > > > -- > > ** Archive: > http://www.mail-archive.com/dev%> 40lists.cfdeveloper.co.uk/ > > > > > To unsubscribe, e-mail: > [EMAIL PROTECTED] > > For additional commands, e-mail: > > [EMAIL PROTECTED] For human help, e-mail: > > [EMAIL PROTECTED] > > > > > > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > > -- > ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ > > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] For human help, e-mail: > [EMAIL PROTECTED] > > > -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED] -- ** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] For human help, e-mail: [EMAIL PROTECTED]
