On Thu, 30 May 2002 14:50:14 -0400, mjs wrote:
>or which values occur more than >once? SELECT serialnum, COUNT (*) FROM tablename GROUP BY serialnum HAVING COUNT (*) > 1 >If the >column is called serialnum, how do write a select statement to find >out which are the 10 most common values? Easy way: just sort in descending order, and look only at page or screen one: SELECT serialnum, COUNT (*) + FROM tablename + GROUP BY serialnum + HAVING COUNT (*) > 1 + ORDER BY 2 DESC Arguably "elegant," "fancy," wizardly way: CREATE TEMP VIEW SNumCounts + (SerialNum, SNFrequency) + AS SELECT + SerialNum, COUNT (*) + FROM tablename SELECT * FROM SnumCounts s1 + WHERE 10 > + (SELECT COUNT (*) + FROM SNumCounts s2 + WHERE s2.SNFrequency > s1.SNFreqency) ORDER BY SNFrequency DESC This means, roughly: "Show me the groups for which there are fewer than 10 other groups with more occurences." Depending on the size of your base table, the number of duplicates, and whether serialnum is indexed, this could be a very slow command. Ties for tenth place also can make this confusing. Bill ================================================ 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/
