how to list the most common record

bex · September 6th, 2008, 11:46 AM

How to write a query to list the most common records in a database?
and how to obtain duplicates in output Like:

FirstName
FirstName
LastName
LastName

any one?

bx

Old Pedant · September 6th, 2008, 02:34 PM

I think you need to explain your terms.

Each record in a table is, by definition, a singleton. So in some sense the count for every record is always 1.

I assume by "most popular", you mean "the value that occurs the most times". But *WHICH* value??? If I have 23 people with the last name "Smith" (and none of them have the same first name) and 47 people with the first name "Mary" (and none of them have the same last name), what is YOUR definition of "most popular"???

And I truly don't understand the second part of your question, at all. Perhaps the best thing to do would be to show some sample data (e.g., records that contain LastName, FirstName, phone, age) and then show your desired output results.

bex · September 7th, 2008, 03:22 PM

ok I have 20 records in my database, 12 of them are retrieved 2 times a day and 8 are retrieved 15 times a day. now how do i retrieve the 8 that are mostly used? This was the first question.

Second question:
if you run SELECT * FROM Employee you will get 20 records, each employee in a row once, i need to show 40 records each employee duplicated, Like;
Dave Marphi
Dave Marphi
Maria Delphino
Maria Delphino etc...

bx

Old Pedant · September 7th, 2008, 05:55 PM

I have 20 records in my database, 12 of them are retrieved 2 times a day and 8 are retrieved 15 times a day. now how do i retrieve the 8 that are mostly used?

You don't. Unless you record all the retrievals in a separate many-to-one table. And your code must do that; databases don't know or care how many times a given record is retrieved.

if you run SELECT * FROM Employee you will get 20 records, each employee in a row once, i need to show 40 records each employee duplicated, Like;
Dave Marphi
Dave Marphi
Maria Delphino
Maria Delphino etc...

Well, this would probably be better done in whatever language you are using to do the reporting. Just display each record twice.

But if you think you really *must* do this in the SQL (ugh), you could presumably do

Code:

   SELECT firstname, lastname FROM employees AS E1
   UNION
   SELECT firstname, lastname FROM employees AS E2
   ORDER BY firstname, lastname

When you use a UNION, the ORDER BY must be after the last of the SELECTs and it applies to *all* the records in the union.

Depending on what DB you are using, you might find that the UNION eliminates the duplicates (completely defeating your purpose). If the DB has a
UNION ALL
then use that to make sure you get all records. If it does not give any choice for you in this matter, then purposely add a dummy field to ensure the records are not the same:

Code:

   SELECT firstname, lastname, 1 AS dummy FROM employees AS E1
   UNION
   SELECT firstname, lastname, 2 AS dummy FROM employees AS E2
   ORDER BY firstname, lastname