Retrieving the last record in each group, older than given time

Question

As in this question: Retrieving the last record in each group, I want to query the latest entries of each group.

In difference to the linked question, I also want to add a criteria for the maximum time.

I have tried the following:

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

I am not sure if this is 100% correct, Is there a better solution? Maybe without the duplicate time <= nnnnnnn condition?

Gordon Linoff · Answer 1 · 2013-04-19T13:02:43.213

0

You can do this in the where clause instead:

select m.*
from messages m
where m.id = (select m2.id
              from messages m2
              where m2.name = m.name and
                    m2.time <= nnnn
              order by m2.time desc
              limit 1
             )

edited Apr 19 '13 at 13:02

answered Apr 19 '13 at 10:55

Gordon Linoff

1,242,037
58
646
786

This gives me a single message (`limit 1`). I need the latest messages of each group – mario.schlipf Apr 19 '13 at 11:22
@schlimpf . . . This is a correlated subquery. It is going to return one value for each group. The performance will be much improved if you have an index on `messages(name, time)` or `message(name, time, id)`. – Gordon Linoff Apr 19 '13 at 13:03
and avoid using a correlated subquery – Strawberry Apr 19 '13 at 13:04

Parley Hammon · Answer 2 · 2017-11-15T22:01:49.610

0

This is what you're looking for. last-n-per-group-less-than-x

 SELECT m1.*
    FROM messages m1
    LEFT JOIN messages m2 ON (
        m2.name = m1.name AND
        IF(m2.date < 'nnnnnnn',m2.date > m1.date, m1.date > 'nnnnnnn')) 
    WHERE m2.idIS NULL

edited Nov 15 '17 at 22:01

answered Nov 15 '17 at 21:55

Parley Hammon

27
1
3

Retrieving the last record in each group, older than given time

2 Answers2