CODEWITHSUNDEEP
mysqlsqlrelational-database
oe a
oe a

Reputation: 2280

Selecting only top result from two table query

Here are my two tables:
Songs:

+--------+---------------------+
| SongID |    SongDeviceID     |
+--------+---------------------+
|   3278 | 1079287588763212246 |
|   3279 | 1079287588763212221 |
|   3280 | 1079287588763212230 |
+--------+---------------------+

Votes:

+--------+--------+
| SongID | UserID |
+--------+--------+
|   3278 |     71 |
|   3278 |     72 |
|   3279 |     71 |
+--------+--------+

I am trying to count the number of entries in the votes table (for each unique SongID and return the SongDeviceID of the entry with the most.

This is what I have so far:

SELECT Songs.SongDeviceID, COUNT(*) FROM Votes INNER JOIN Songs ON Votes.SongID = Songs.SongId GROUP BY Votes.SongID, Songs.SongDeviceID ORDER BY count(*) DESC

Which returns:

+---------------------+----------+
|    SongDeviceID     | Count(*) |
+---------------------+----------+
| 1079287588763212246 |        2 |
| 1079287588763212221 |        1 |
+---------------------+----------+

UPDATE: The query has been updated to take care of getting the SongDeviceID but I still need help returning only the first row.

Upvotes: 0

Views: 77

Answers (3)

M Akela
M Akela

Reputation: 249

for first

SELECT  SongID,COUNT(*) from votes GROUP BY SongID order by count(*) DESC LIMIT 1

and you want song device name then 

SELECT SongID,COUNT(*),SongDeviceID
from
  votes 
left join 
   Songs on votes.songid = song.songid
GROUP BY
   SongID 
order by 
   count(*) DESC LIMIT 1

Upvotes: 1

Justas
Justas

Reputation: 132

select top 1
    S.SongDeviceID, COUNT(distinct V.UserID) as 'UserVotes'
from
   Songs as S
join 
  Votes as V on V.SongID = S.SongID
group by
   S.SongDeviceID
order by
  UserVotes DESC

Upvotes: 0

plain jane
plain jane

Reputation: 1007

Try this

   SELECT Songs.SongDeviceID, COUNT(*) FROM Votes INNER JOIN Songs ON Votes.
    SongID =  Songs.SongId GROUP BY Votes.SongID, Songs.SongDeviceID ORDER BY count(*) 
    DESC LIMIT 1

Upvotes: 1

Related Questions