Member Avatar for bibiki

Hey there, I need help with a query. The typical situation is like this:

I expect, say, 1000 rows out of a query that is based, say, on lastName column. One column is, say, firstName. I know I only have like twenty unique names that occur on multiple rows. I need to retrieve the five most frequent firstName-s in the 1000 row query result. can someone please guide me with this?

SELECT firstName FROM someTable WHERE lastName [meets some criteria] _______(what would allow me to retrieve the five most frequent firstName-s)

Any help greatly appreciated.

  • 3 Contributors
  • 3 Replies
  • 143 Views
  • 28 Minutes Discussion Span
  • Latest Post Latest Post by bibiki
Member Avatar for Lucaci Andrew

Maybe this would help you: Top clause
in MySql is LIMIT number instead of top. Lookup the syntax.

Edited by Lucaci Andrew
Member Avatar for pritaeas

Something like this:

SELECT firstName, COUNT(*) AS nameCount 
FROM someTable
WHERE lastName = ''
GROUP BY firstName
ORDER BY nameCount DESC LIMIT 5
Member Avatar for bibiki

thank you both very much. I'm using pritaeas' code and certainly reading the info Lucaci andrew posted.

thanks again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.