CODEWITHSUNDEEP
mysql
lisovaccaro
lisovaccaro

Reputation: 33996

Get row with biggest column value when there are similar rows?

I'm working on a system to add Tags to my site, right now this is my "Tags" table:

URL           | Tag     | UID
example1.com    humor     123
example1.com    humor     342
example1.com    humor     234
example1.com    blog      123
example1.com    blog      432
example1.com    food      221
example2.com    blog      432

I'm using the following query to sum tags for each site:

SELECT *, COUNT(*) as SUM
FROM History
GROUP BY URL, UID

So I get:

URL           | Tag     | Sum
example1.com     humor    3
example1.com     blog     2
example1.com     food     1
example2.com     blog     1

Finally (and this is what I don't know how to do) I need to get only the 2 rows with the biggest Sum for each grouped URL so I know what's the actual subject of the site:

URL           | Tag     | Sum
example1.com     humor    3
example1.com     blog     2
example2.com     blog     1

How can I do this?

Upvotes: 1

Views: 92

Answers (1)

imm
imm

Reputation: 5919

Try using a self-join on your results. This article has some great examples. Rather than nesting a number of queries, it might be easier (and faster) to create a temporary table from your first query, and then using that for the second step (getting the max "sum" from each group). See the section of that page titled "Select the top N rows from each group".

Upvotes: 2

Related Questions