Distinct or group by no working in mysql table

Question

Here is a sample data set.

id list_id user_id times
 2       1       8 2017-05-25 10:27:18
 4       2       8 2017-05-26 12:58:58
 6       1       8 2017-06-09 17:28:00
 7       2       8 2917-06-09 19:27:36

I have multiple list_id in table. i want to fetch only a single list_id on the basis of latest date. Please help me guys. I am using this query but i am not succeed.

 select * 
  from class_history 
 where user_id=8 
 GROUP 
  BY list_id 
 ORDER by id DESC

or

select *
   from class_history 
 where user_id=8 
 GROUP 
    BY list_id 
 ORDER by time DESC

Thanks for your support.

ka_lin · Accepted Answer · 2017-06-12T07:14:18.713

-1

You have distinct id's so you won't be able to get distinct list_id's, you would need to do:

SELECT B.* FROM
(
    select list_id,user_id ,max(times) times
    from class_history 
    where user_id=8
    group by list_id 
) A INNER JOIN class_history B USING (list_id,user_id,times);

edited Jun 12 '17 at 07:14

answered Jun 12 '17 at 06:39

ka_lin

9,329
6
35
56

1

An explanation for the -1 would be appreciated – ka_lin Jun 12 '17 at 06:49
i also want to fetch my recent time. for display my recent history. – Vivek Mishra Jun 12 '17 at 06:52
The downvote relates to your original answer. – Strawberry Jun 12 '17 at 06:57
@ka_lin working perfect but its fetch only one row whose list_id is 2. – Vivek Mishra Jun 12 '17 at 07:04
And you want the time as well,yes? – ka_lin Jun 12 '17 at 07:06
@ka_lin yes i want also times. means all row. – Vivek Mishra Jun 12 '17 at 07:09
tested, should be fine – ka_lin Jun 12 '17 at 07:14

Distinct or group by no working in mysql table

1 Answers1