Look at the query in parts, from the outside in: First you do a "Left(your_column_name,1)" - this returns a result set containing EVERY first letter of that column. If you have 1000 rows in the table, then this (hidden) result set is 1000 rows.
Next you wrap that in "DISTINCT". This says "only return unique values". So this whittles away duplicates returning you only a list (column) of all the first letters (characters, really) in the data. Functions (like LEFT()), in SQL, are performed (generally) on the data BEFORE it's SELECTED - in other words they (seemingly) change the data then allow selections based on those changes. Bens's (I think) suggestions were also valuable. You should also alias the column using "AS" and order it (as it has no innate order) like this: SELECT DISTINCT(Left(your_column_name,1)) AS FirstChar FROM your_table_name ORDER BY FirstChar Also note that this does indeed return the characters, not the first letters. So if you have values beginning with spaces, numbers, symbols, etc they will be included. Here's another version that you may find useful: SELECT LEFT(your_column_name, 1) AS FirstChar, COUNT(LEFT(your_column_name, 1)) AS CNT FROM your_table_name GROUP BY LEFT(your_column_name, 1) ORDER BY FirstChar This one also returns, in the "CNT" column, then number of records that begin with that character. This can be useful in determining pagination needs for the output. Note that the GROUP BY clause is grouped by the function you performed - this is because during processing the engine really isn't working on the data that you have in your table, but rather on the temporary data created by your function. (As an aside, you can't use the name you've assigned to the column because that isn't applied until the record set is complete - before it's ordered.) Jim Davis > -----Original Message----- > From: Jeff [mailto:[EMAIL PROTECTED] > Sent: Friday, August 29, 2003 11:13 AM > To: CF-Talk > Subject: Re: Okay, what's the first part of those alphabetical navigation > strips? > > on 8/29/03 10:50 AM, Ben Doom at [EMAIL PROTECTED] wrote: > > > Just call the query once. It will return all of the letters that start > > names. > > > > I think you might need an "as outputname" clause to make it work. I'd > > certainly add an "order by" clause to put the letters in order. > > > Hmm... > > I can do this: > > SELECT DISTINCT(Left(your_column_name,1)) > FROM your_table_name > > And it returns all letters who have an entry. With no WHERE clause. Maybe > I'm just not really understanding how this query works because that output > was a total surprise...lol. Now, I see the output skips over a letter > that's > not in the table. That's good. I get output with 22 records. I'm thinking > now I have to somehow loop over this recordset or somehow check the > recordset with the following logic: > > <cfif there's a entry in the recordset that contains the letter "LETTER"> > <a href"page.cfm?letter=LETTER">LETTER</a> > <cfelse> > LETTER > </cfif> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:4 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
