CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Gündoğdu Yakıcı
Gündoğdu Yakıcı

Reputation: 677

How do I change the order before MySQL grouping?

If there is more than 1 identical number in the table, I only bring one. But before bringing it, the first added date comes from the records. I want the last added record to come.

Here is an example;

SELECT * FROM comments WHERE phone_number='01234'

Output:

id | phone | created_at
-----------------------
1   01234   2020-10-27
2   01234   2020-10-28

MySQL code;

SELECT * FROM comments GROUP BY phone_number ORDER BY created_at DESC

MySQL output;

id | phone | created_at
-----------------------
1   01234   2020-10-27

Upvotes: 0

Views: 56

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want the most recent row, you don't want aggregation. Instead, filter for it:

select c.*
from c
where c.created_at = (select max(c2.created_at)
                      from comments c2
                      where c2.phone = c.phone
                     );

Or use window functions:

select c.*
from (select c.*,
             row_number() over (partition by phone order by created_at desc) as seqnum
      from comments c
     ) c
where seqnum = 1;

Or, if you want this just for one phone, then you can use order by and limit:

select c.*
from c
where phone = '3244003390'
order by c.created_at desc
limit 1;

Upvotes: 2

Rodrigo Chapeta
Rodrigo Chapeta

Reputation: 119

Something along this lines, using id since its faster and there is no real reason to use created_at:

SELECT t.*  FROM  comments t 
        WHERE t.id = (SELECT MAX(t2.id) FROM comments t2
        WHERE t2.phone = t.phone)

Upvotes: 1

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

Since MySQL 8.0 you can use window functions for this purpose:

select * from (
  select 
      comments.*,
      row_number() over (partition by phone order by created_at desc) row_num
  from comments
) c where row_num = 1;

Test it on SQLize.online

Just in case then created_at column correlate with id you can use next approach:

select 
    max(id) id,
    phone,
    max(created_at) created_at
from comments
group by phone;

It returns last id and last created_at values for each phone and works with old MySQL versions.

SQLize.online

Upvotes: 1

Related Questions