Group by last occurrence

Question

i am trying to get the last rows where rec_p_id = 4 SORTED by the timestamp. Since i do not want to have all the results WHERE rec_p_id = 4, i am using GROUP BY to group it by send_p_id.

My SQL query looks like this:

SELECT * 
  FROM 
     ( SELECT * 
        FROM chat 
      WHERE rec_p_id= "4" 
      ORDER 
        BY timestamp DESC) as sub 
 GROUP 
    BY send_p_id

My table looks like this:

Table chat

c_id	send_p_id	rec_p_id	timestamp
1	3	4	2020-05-01 14:46:00
2	3	4	2020-05-01 14:49:00
3	3	4	2020-05-01 14:50:00
4	7	4	2020-05-01 12:00:00
5	4	7	2020-05-01 12:10:00
6	7	4	2020-05-01 12:20:00
7	9	4	2020-05-01 16:50:00
8	9	4	2020-05-01 17:00:00

I want to get the last occurrences:

c_id	send_p_id	rec_p_id	timestamp
3	3	4	2020-05-01 14:50:00
6	7	4	2020-05-01 12:20:00
8	9	4	2020-05-01 17:00:00

But instead i get all the first ones:

c_id	send_p_id	rec_p_id	timestamp
1	3	4	2020-05-01 14:46:00
4	7	4	2020-05-01 12:00:00
7	9	4	2020-05-01 16:50:00

I saw the query i am using in this question: ORDER BY date and time BEFORE GROUP BY name in mysql

it seems to work for all of them. What am i doing wrong with my query? Thanks in advance.

Whatever you think this query does, I'm afraid you're mistaken. See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Feb 06 '21 at 21:10
@Strawberry Thanks for your comment. I will keep that in mind the next time. — aajuu19, Feb 06 '21 at 21:48

ScaisEdge · Accepted Answer · 2021-02-07T07:25:00.417

0

Looking to your expected result seems you are looking for

select max(c_id) c_id, send_p_id, min(timestamp) timestamp
from chat WHERE rec_p_id= "4" 
group by send_p_id
ORDER BY c_id

Group by is for aggregated result ..
an use without aggregation function can produce unpredicatble result and in version > 5.6 can produce error

edited Feb 07 '21 at 07:25

answered Feb 06 '21 at 20:59

ScaisEdge

131,976
10
91
107

Thank you, that worked for me. I guess i still have a lot to learn in SQL. – aajuu19 Feb 06 '21 at 21:38

Farrukh Normuradov · Answer 2 · 2021-02-06T21:27:51.763

I used this answer and built this setup for you.
The code below is the copy of it, so that you can run it later yourself. For the solution the example from the official manual.

CREATE TABLE chat
(
        c_id INT PRIMARY KEY
    ,   send_p_id INT
    ,   rec_p_id INT
    ,   timestamp DATETIME
     
);

INSERT INTO chat VALUES
    (1, 3,  4,  '2020-05-01 14:46:00')
,   (2, 3,  4,  '2020-05-01 14:49:00')
,   (3, 3,  4,  '2020-05-01 14:50:00')
,   (4, 7,  4,  '2020-05-01 12:00:00')
,   (5, 4,  7,  '2020-05-01 12:10:00')
,   (6, 7,  4,  '2020-05-01 12:20:00')
,   (7, 9,  4,  '2020-05-01 16:50:00')
,   (8, 9,  4,  '2020-05-01 17:00:00');

Solution:

SELECT c_id,
       send_p_id,
       rec_p_id,
       timestamp
FROM   chat AS c
WHERE  timestamp=(SELECT MAX(c1.timestamp)
              FROM chat AS c1
              WHERE c.send_p_id = c1.send_p_id)
AND send_p_id != 4
ORDER BY timestamp;

Hello Farukh and thanks for your answer. Your solution worked as well for me! Thanks to you and scais. Can i quick-check which of these queries is faster? — aajuu19, Feb 06 '21 at 21:42
Maybe [this](https://dev.mysql.com/doc/refman/8.0/en/using-explain.html) can help you. — Farrukh Normuradov, Feb 06 '21 at 21:51

Group by last occurrence

2 Answers2