You need to do this in 3 steps.
1. Create the following view:
CREATE VIEW tempSeq (serialnum, CountSerNo) AS SELECT SerialNum,
COUNT(*) FROM TableName GROUP BY SerialNum
2. Create another view based on this view:
CREATE VIEW tempSeq2 (serialnum, CountSerNo) AS SELECT
SerialNum, CountSerNo) FROM tempSeq ORDER BY CountSerNo DESC
3. Finally run the following query
SELECT SerialNum, CountSerNo FROM tempSeq2 WHERE LIMIT = 10
I have tried this on a small set of data and it seems to work.
Stephen R. Hartmann
SQL Resources Group
[EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
On Behalf Of mjs
Sent: Thursday, May 30, 2002 1:50 PM
To: Rbase List Server; rStreet yahoo
Subject: SQL Wizards....please advise!
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/
