0

I have a database full of media. For simplicity sake, lets say it has 

Table: media

id
college_id
title
shares

I'm trying to get the articles with the most amount of shares from each college. So, in essence, get the top shared article from Cornell, USC, Syracuse.

I have been using this SQL command, but it doesn't return the most shared article from each college.

SELECT *
FROM media
GROUP BY college_id
ORDER BY shares DESC

Anyone have any ideas?

The shares column is an integer indicating the number of shares.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
Brian Weinreich
  • 7,692
  • 8
  • 35
  • 59

3 Answers3

4
    SELECT top 1 * from media group by college_id order by shares desc

That was my first thought... though I'm wondering if I'm missing something since this answer is a lot simpler than Adam's. If shares is the number of times it's been shared though this sound like what you're looking for.

Edit: I see now. Here's another way...

    select m1.id, m1.college_id, m1.title, m1.shares
    from media m1 join
    (
        select college_id, max(shares) max_shares
        from media
        group by college_id

    ) m2 on m1.college_id = m2.college_id and m1.shares = m2.max_shares

This will return more than one article per college in cases where there is a tie. I don't if you want that or not.

Brandon Moore
  • 8,590
  • 15
  • 65
  • 120
  • This is pretty similar to what Adam Wenger did... just without the CTE and without the partition. Shouldn't be anything here that wouldn't work on MySql. – Brandon Moore Nov 15 '11 at 02:50
  • This worked great!! Appreciate it! Thanks for the help to everyone else as well. Sorry for not specifying this was MySQL upfront (I'll make sure to next time). – Brian Weinreich Nov 15 '11 at 03:14
  • Thanks Adam. @BrianW Probably better that you didn't or I might not have looked at it :) – Brandon Moore Nov 15 '11 at 03:18
0

If we assume that shares column handles the share count query would be,

SELECT title, 
       Max(shares) AS max 
FROM   media 
GROUP  BY college_id 
ORDER  BY max DESC

But If we assume that shares column is an ID or sth, query would be,

SELECT title, 
       COUNT(shares) AS COUNT 
FROM   media 
GROUP  BY college_id 
ORDER  BY COUNT DESC

I suggest you to give more details next time.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
tuze
  • 1,978
  • 2
  • 15
  • 19
  • These queries will give the total count of all shares of all articles within a college, it will not tell which article had the most shares. – Adam Wenger Nov 15 '11 at 02:27
  • shares column holds an integer indicating the number of shares. I tried your first query, but added title to it.. and it still shows the wrong title for the result `SELECT MAX(shares),title as max from media group by college_id order by max desc;` – Brian Weinreich Nov 15 '11 at 02:28
  • This `SELECT title,MAX(shares) as max from media group by college_id order by max desc` is what I was trying as well. It just doesn't work. :( It doesn't show the correct title for the row. – Brian Weinreich Nov 15 '11 at 02:33
  • @BrianW Do different ids have same title? – tuze Nov 15 '11 at 02:36
  • Nope, all ids have different titles. For example id:1,title:how to get a job,college_id:4 – Brian Weinreich Nov 15 '11 at 03:13
  • Shouldn't make a difference if different id's can have the same title (assuming they are not duplicates). – Brandon Moore Nov 15 '11 at 03:14
0

You are looking for the MAX sql keyword

In MSSQL this works just fine

SELECT college_id, 
       Max(shares) 
FROM   media 
GROUP  BY college_id
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
Don Dickinson
  • 6,200
  • 3
  • 35
  • 30