Get last 5 rows from Table for each user

Question

I need you help with this problem.

Scenario:

Users can vote any number of songs, but I only want to select the last 5 votes per user. I do not want to select the last 5 votes.

Table structure:

klick here

Example:

3 users

Karl, voted 10 songs
Robert, voted 6 songs
Joanne, voted 3 songs

I want to get Karls last 5 votes, Roberts last 5 votes and Joannes 3 votes.

Current SQL query:

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
FROM T_Songs AS songs
RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id
WHERE votes.P_Vote_id IN (
    SELECT P_Vote_id
    FROM T_Votes
    GROUP BY F_User_id
    HAVING P_Vote_id > MAX(P_Vote_id)-5

);

But this query doesn't return the right vote count.

Solution (Thanks to Gordon Linoff and Paul Spiegel):

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
FROM T_Songs AS songs
RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id
WHERE users.nobility_house IS NOT NULL
AND votes.P_Vote_id >= coalesce(
(select votes2.P_Vote_id
from T_Votes votes2
where votes2.F_User_id = votes.F_User_id
order by votes2.P_Vote_id desc
limit 1 offset 4
), 0)
GROUP BY votes.F_User_id

Gordon Linoff · Accepted Answer · 2018-06-07T02:23:10.830

1

Assuming "last 5" means the five with the highest ids, you can do:

select v.*
from t_votes v
where v.p_vote_id >= (select v2.p_vote_id
                      from t_votes v2
                      where v2.f_user_id = v.f_user_id
                      order by v2.p_vote_id desc
                      limit 1 offset 4
                     );

I'll let you figure out how to bring in the columns from the other tables.

EDIT:

If there are fewer than 5 rows:

select v.*
from t_votes v
where v.p_vote_id >= coalesce( (select v2.p_vote_id
                                from t_votes v2
                                where v2.f_user_id = v.f_user_id
                                order by v2.p_vote_id desc
                                limit 1 offset 4
                               ), p_vote_id
                             );

edited Jun 07 '18 at 02:23

answered Jun 06 '18 at 15:40

Gordon Linoff

1,242,037
58
646
786

I'd use `v.p_vote_id >= coalesce(, 0)` for the case, that there are less than 5 rows. – Paul Spiegel Jun 06 '18 at 15:49
̶t̶̶h̶̶a̶̶n̶̶k̶̶ ̶̶y̶̶o̶̶u̶̶,̶̶ ̶̶b̶̶u̶̶t̶̶ ̶̶t̶̶h̶̶i̶̶s̶̶ ̶̶q̶̶u̶̶e̶̶r̶̶y̶̶ ̶̶o̶̶n̶̶l̶̶y̶̶ ̶̶r̶̶e̶̶t̶̶u̶̶r̶̶n̶̶s̶̶ ̶̶t̶̶h̶̶e̶̶ ̶̶l̶̶a̶̶s̶̶t̶̶ ̶̶5̶̶ ̶̶v̶̶o̶̶t̶̶e̶̶s̶̶ ̶̶f̶̶o̶̶r̶̶ ̶̶o̶̶n̶̶e̶̶ ̶̶u̶̶s̶̶e̶̶r̶̶,̶̶ ̶̶b̶̶u̶̶t̶̶ ̶̶n̶̶o̶̶t̶̶ ̶̶f̶̶o̶̶r̶̶ ̶̶e̶̶v̶̶e̶̶r̶̶y̶̶ ̶̶u̶̶s̶̶e̶̶r̶̶,̶̶ ̶̶t̶̶h̶̶i̶̶s̶̶ ̶̶i̶̶s̶̶ ̶̶m̶̶y̶̶ ̶̶p̶̶r̶̶o̶̶b̶̶l̶̶e̶̶m̶̶.̶̶ ̶̶h̶̶o̶̶w̶̶ ̶̶t̶̶o̶̶ ̶̶d̶̶o̶̶ ̶̶t̶̶h̶̶a̶̶t̶̶?̶ - Paul Spiegel's answer solved problem. – anghenfil Jun 06 '18 at 16:01

score 0 · Answer 2 · answered Jun 06 '18 at 15:46

You can filter with a join on the same table :

SELECT songs.genre, songs.title, songs.band, COUNT(*) AS anzahlVotes, users.name
    FROM T_Songs AS songs
    RIGHT JOIN T_Votes AS votes ON songs.P_Song_id = votes.F_Song_id
    LEFT JOIN T_Users AS users ON votes.F_User_id = users.P_User_id        
LEFT JOIN (
    SELECT F_User_id, Max(P_Vote_id)-5 as MaxVoteId
    FROM T_Votes
    GROUP BY F_User_id
    ) DF ON ((DF.F_User_id = votes.F_User_id) AND (P_Vote_id >= MaxVoteId));

Get last 5 rows from Table for each user

2 Answers2