Using Order by And Group By Together Eliminates some Records

Question

user_id      date      
-------    ---------
1          1551867583
2          1551867580
3          1551867573
2          1551867543
2          1551867521

I have a similar table structure. What I want to do is to select records per user and select the latest one of each user's record.

Tried

GROUP BY profile_visits.user_id ORDER BY profile_visits.date DESC

This doesn't show all the latest records.

I searched and found this on StackOverflow

group by and order by in mysql query

However, that solution didn't work for me

Any idea how to solve this?

SO doesn't like "didn't work for me". SO likes "didn't work for me because...". See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Mar 06 '19 at 10:35
I don't exactly know why it didn't work thats why I didn't write "because" ! — Utku Dalmaz, Mar 06 '19 at 10:39

ScaisEdge · Accepted Answer · 2019-03-06T10:45:04.350

0

You can use max date and group by

select max(date) max_date, user_id 
from  my_table 
group by user_id  
order by user_id, max_date

edited Mar 06 '19 at 10:45

answered Mar 06 '19 at 10:44

ScaisEdge

131,976
10
91
107

I guess max doesn't work with only unix timestamp? – Utku Dalmaz Mar 06 '19 at 10:44
@UtkuDalmaz max() is max for each data type .. and unixtimestamp is just an numeric value – ScaisEdge Mar 06 '19 at 10:45
"MAX( profile_visits.date ) AS last_visit_date_time GROUP BY profile_visits.user_id ORDER BY last_visit_date_time DESC" When I run this I cannot see latest records – Utku Dalmaz Mar 06 '19 at 10:47
youre code .. is not the code in your sample .. anyway .. my answer retunr the last date for each user .. – ScaisEdge Mar 06 '19 at 10:52
OH It worked after I add user_id to order by. Thank you !! – Utku Dalmaz Mar 06 '19 at 10:53

Using Order by And Group By Together Eliminates some Records

1 Answers1