Finding the top 10 most common occuring items is very difficult, but if you want all the items which have more than 10 rows, you can do this.
select serialnum, count (*)=s from mytable group by serialnum having count (*) > 10 Troy ===== Original Message from [EMAIL PROTECTED] at 5/30/02 12:50 pm >Ok....I need a little education with regards to "fancy" syntax, using (I >think) group by and having. Lets say I have a table with just 1 column, >an integer column, and there are a few thousand rows. I want to see >which values are the most common. I know I can do a tally, but that >will be sorted by the values in the column, not the frequency. If the >column is called serialnum, how do write a select statement to find out >which are the 10 most common values? or which values occur more than >once? I know I can send the output of the tally to a file, import the >file, and sort the data, but that seems less than elegant. How do you >wizards do it?? > >TIA! > >Mike Sinclair > >================================================ >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/
