Advanced query with mysql: variables/subqueries?

Question

I want to get the latest 5 messages in the inbox. To get the 5 latest IDs i need to use this:

SELECT
    MAX(id)
FROM 
    samtaler
WHERE 
    brukerid_mottaker = 1 
GROUP BY brukerid_avsender
ORDER BY id DESC
LIMIT 5

This return the correct ID's I need. But in the same query i want to select data from the same table, the row that got the id that returned from this query above.

I have tried out some things, variables and self-join but no luck:

select 
    p2.title, 
    p2.message,
    @a:=max(p1.id)
from 
    samtaler p1
    join samtaler p2
    on (@a = p2.id)
where
    p2.brukerid_mottaker = 1 
group by p2.brukerid_avsender
order by p2.id DESC
limit 5

Why isnt this working?

This is the current data in the database: I want to return in this case, row 13 and 4. Sorry for bad english.

Michael Berkowski · Accepted Answer · 2012-06-22 18:19:38Z

1

Join against a subquery, instead of a plain self join. An IN() clause won't work since LIMIT cannot be used inside an IN(). It should work in the joined subquery though:

SELECT
  p1.title,
  p1.message,
  p2.id
FROM 
  samtaler p1
  JOIN (
    SELECT MAX(id) AS id
    FROM 
        samtaler
    WHERE 
        brukerid_mottaker = 1 
    GROUP BY brukerid_avsender
    ORDER BY id DESC
    LIMIT 5
  ) p2 ON p1.id = p2.id

By this method, there is no need for variables.

answered Jun 22, 2012 at 18:19

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Advanced query with mysql: variables/subqueries?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related