order and group mysql

Question

i am beginner in query mysql

i have table rows and value like this

i want to select from the table : order by ID descending and group by Phone so result will be like this

pls any body help me..

i already put like this

select * from messages where 1 group by phone order by ID desc

but its wrong

thank you

You should use group by with aggregate function, refer https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html — Alpesh Jikadra, May 24 '18 at 04:59
@Defr - Your result image seems not matching with what you have written you want to do. — Sudhir Sapkal, May 24 '18 at 05:10

score 3 · Accepted Answer · answered May 24 '18 at 05:22

To get latest message per phone attribute you can use a self join

select a.*
from messages a
join (
    select phone, max(id) id
    from messages
    group by phone
) b on a.phone = b.phone and a.id = b.id

Or using a left join

select a.*
from messages a
left join messages b on a.phone = b.phone and a.id < b.id
where b.phone is null

Sudhir Sapkal · Answer 2 · 2018-05-24T05:25:33.833

2

Try the below query

select phone, text from messages group by phone order by id DESC

edited May 24 '18 at 05:25

answered May 24 '18 at 05:03

Sudhir Sapkal

as the text is not aggregate function so it will give you an error when you run it. – Rajat Jaiswal May 24 '18 at 05:19
Where I have written text as aggregate function ? – Sudhir Sapkal May 24 '18 at 05:26
I mean to say it should come it group by clause like phone – Rajat Jaiswal May 24 '18 at 05:34
@RajatJaiswal - For what purpose ?? In question it mentioned the result should be group by phone no only. If I am reading it correctly :) – Sudhir Sapkal May 24 '18 at 05:38

score 0 · Answer 3 · answered May 24 '18 at 05:18

0

It will not work with the group by instead of this try below query

SELECT text,phone
FROM @tblPhone tmp
WHERE id = (SELECT MAX(Id) FROM @tblPhone tmp1 WHERE tmp1.PHONE = tmp.Phone)
ORDER BY Id desc

answered May 24 '18 at 05:18

Rajat Jaiswal

3 Answers3