2

According to this answer, the best way to get the latest record in each group is like this:

SELECT m1.*
FROM messages m1 
LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id)
WHERE m2.id IS NULL

I have tried this and it works great.

However, I also need to check whether the latest record is the only record in the group. I tried modifying the query to this:

SELECT m1.*, COUNT(m3.name)
FROM messages m1 
LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id)
LEFT JOIN messages m3 ON m1.name = m3.name
WHERE m2.id IS NULL

But it only returns one row.

If I remove the COUNT() statement, leaving us with this:

SELECT m1.*
FROM messages m1 
LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id)
LEFT JOIN messages m3 ON m1.name = m3.name
WHERE m2.id IS NULL

Duplicate rows are returned, so apparently the additional LEFT JOIN messes up the query.

Is there a simple way to check whether the latest record returned is the only record in the group? A simple bool value would be fine, or the number of records in the group would work, too.

EDIT: The reason I'm trying to do this is that I'm writing a commenting system and I want users to be able to edit comments. When a comment is edited, I want to display a link showing it was edited that, when clicked, takes you to a page showing the edits (like on facebook, or how the revision system works for questions on stackoverflow). So I need to get the latest revision of each comment, as well as an indicator letting me know whether there are multiple revisions of the comment (so I know whether or not to show an "edited" link). The solution needs to be efficient, since there might be hundreds of comments in a thread.

Community
  • 1
  • 1
Nate
  • 26,164
  • 34
  • 130
  • 214

3 Answers3

3

Try:

SELECT m1.*, m2.total
FROM messages m1,
(select max(id) id, count(*) total, name
 from messages
 group by name) m2
where m1.name = m2.name and m1.id = m2.id

You can convert this to join syntax if you want, but the idea is to run a subquery and join once instead of twice and use only equality joins which could give you a performance boost. I would benchmark both my solution and Aquillo's and see which is faster in your case.

Vulcronos
  • 3,428
  • 3
  • 16
  • 24
  • How would you convert this to join syntax? I've been trying, but cannot for the life of me figure it out with the `max` in there :-) – Nate Jun 14 '14 at 17:11
  • Oh wow, lol, I just tried replacing the comma after m1 with JOIN and apparently that's all that had to be done! (I was trying to rewrite it in a clearly wrong manner). Thanks! – Nate Jun 14 '14 at 17:17
1

Not tested but I guess something like this would do:

SELECT DISTINCT m1.*
, CASE 
    WHEN m3.id IS NULL 
    THEN 'only record with this name' 
    ELSE 'not only record with this name' 
    END
FROM messages m1 
LEFT JOIN messages m2 ON (m1.name = m2.name AND m1.id < m2.id)
LEFT JOIN messages m3 ON (m1.name = m3.name AND m1.id > m3.id)
WHERE m2.id IS NULL

First LEFT JOIN + WHERE says "only give me the record with given name where there's no higher id".

Second LEFT JOIN says "give the records with given name and a smaller id". Since there might be more records, I've used this together with the DISTINCT. Last the CASE WHEN THEN END determines whether there was any smaller id at all.

Menno
  • 12,175
  • 14
  • 56
  • 88
1

would something like this work for you

with cteMessages  as 

(select Name, max(Id) as LatestId, count(Id) as CountIds
from [Messages]
group by Name)

select * 
from  cteMessages as c
    inner join [Messages] as m 
        on c.Name = m.Name
        and c.LatestId = r.Id