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 SongID,COUNT(*) from votes GROUP BY SongID order by count(*) DESC
Which returns:
+--------+----------+
| SongID | Count(*) |
+--------+----------+
| 3278 | 2 |
| 3279 | 1 |
+--------+----------+
How to return only the first , the highest?
How to return the SongDeviceID from the song table opposed to the SongID?
No comments:
Post a Comment