Rommel SELECT DISTINCT ZipCode, COUNT(*) AS tmpCount FROM MyTable ORDER BY tmpCount DESC GROUP BY ZipCode
Very clever - you taught me something!! I didn't realize you could put an alias in and then use it in the ORDER by. The DISTINCT is unnecessary, since GROUP BY automatically creates distinct values. David Blocker ----- Original Message ----- From: "Rommel Relosa" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 26, 2002 6:12 PM Subject: RE: Counting Groups - 6.5+ > David, > > > Can't I also use: > > > SELECT DISTINCT ZipCode, COUNT(*) AS tmpCount > FROM MyTable > ORDER BY tmpCount DESC > GROUP BY ZipCode > > > Regards, > > Rommel > > > > PS: Yes, I omitted the continuation characters... :p > > > > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On > Behalf Of David M. Blocker > Sent: Thursday, September 26, 2002 3:38 AM > To: [EMAIL PROTECTED] > Subject: Re: Counting Groups - 6.5+ > > > SELECT ZipCode, Count (ZipCode) GROUP BY ZipCode > > Looks like no one read the original message carefully - the original request > was to get counts IN ORDER FROM MOST FREQUENTLY USED TO LOWEST: > > So you need: > > SELECT ZipCode, Count (*) GROUP BY ZipCode ORDER BY 2 DESC > > The "2" refers to the "second item after the SELECT command", which in this > case is the count > > David Blocker > > ----- Original Message ----- > From: "Lawrence Lustig" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Thursday, September 26, 2002 12:10 PM > Subject: Re: Counting Groups - 6.5+ > > > > << > > I also tried a SELECT ZipCode, Count (ZipCode) GROUP BY COUNT > (ZipCode).... > > and also received an error message. > > > > Any suggestions? > > >> > > > > SELECT ZipCode, Count (ZipCode) GROUP BY ZipCode > > > > -- > > Larry > > > > > > ================================================ > > TO SEE MESSAGE POSTING GUIDELINES: > > Send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: INTRO rbase-l > > ================================================ > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > > In the message body, put just two words: UNSUBSCRIBE rbase-l > > ================================================ > > TO SEARCH ARCHIVES: > > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
