Here is a simple solution:
Table: Car
Column: SerNum
Create a view using:
CREATE VIEW SerCount (SerNum, Counter) +
AS SELECT SerNum, COUNT(SerNum) +
FROM Car +
GROUP BY SerNum +
ORDER BY 2=D
Then you can create reports off of this. Like:
PRINT Top10Ser WHERE LIMIT=10
Or
PRINT MultiSer WHERE Counter>1
I am sure there are more elegant ways, but that would probably be the
simplest.
Bill
-----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/
