1

I have a this table:

posts
id    | msg        | topicId
 1    | Hello World| 1
 2    | Whats up?  | 2
 3    | lorem ipsum| 1
 4    | new Topic  | 3
 5    | Dolor sit  | 1
 6    | some text  | 3
 7    | response   | 2
 8    | asdf       | 3

I want to get the row with the biggest id for every topicId like this:

result
id    | msg        | topicId
 5    | Dolor sit  | 1
 7    | response   | 2
 8    | asdf       | 3

My problem is I don't have a clue how to query for the result I want. I'm using symfony 2.3 and doctrine, so if you could use the doctrine query builder code for the answer that would be great :)

Any idea to do this efficiently?

Kable
  • 117
  • 5
  • 13
  • The duplicate question shows how to do it with regular MySQL. I don't know Doctrine, but you should be able to translate the solutions there. You can also search SO for `[mysql] [greatest-n-per-group]`, maybe some of the other questions use Doctrine. This is an EXTREMELY common question. – Barmar Dec 15 '13 at 10:25
  • Sorry for the duplicate. I didn't know what to search for – Kable Dec 15 '13 at 10:55

2 Answers2

2

this a way to go 

SELECT
posts.*
FROM
posts
JOIN (
SELECT MAX(id) AS id ,`msg`  , topicId
FROM posts 
GROUP BY `topicId`
) maxid

 ON posts.`topicId` = maxid.`topicId` 
    AND posts.id = maxid.id

DEMO HERE

echo_Me
  • 37,078
  • 5
  • 58
  • 78
2
SELECT m1.*
FROM YourTable T1
    LEFT JOIN YourTable T2 ON (T1.id= T2.id AND T1.TopicId < T2.TopicId)
WHERE T2.id IS NULL;
user3104183
  • 408
  • 1
  • 9
  • 26
  • This solution works perfectly! Now I just need to do this in doctrine query builder :) – Kable Dec 15 '13 at 10:55
  • 1
    i dont know how this solved your issue by the way , it will give unknown table m1 and even if you change it to t1 it will give all rows [look here](http://sqlfiddle.com/#!2/ff4914/4) – echo_Me Dec 15 '13 at 11:01