CODEWITHSUNDEEP
mysqlsqlmaxdistinct
simon
simon

Reputation: 33

Select DISTINCT and MAX in the same query and list all value in rows attach

Im trying to combine distinct and max assign_id or last assign_id in table below but doesnt get a right value.

Table_task

ASSIGN_ID  |  DRV_ID   | VEHICLE_ID
-------------------------------------- 
    1      |    EFFA   |   1000  
    2      |    SAM    |   1001 
    3      |    FIZA   |   1004 
    4      |    JIJO   |   1000 
    5      |    LISA   |   1000

How to get value display as below ? 

ASSIGN_ID   |  DRV_ID  |  VEHICLE_ID 
----------------------------------------- 
     2      |   SAM    |   1001  
     3      |   FIZA   |   1004 
     5      |   LISA   |   1000

Upvotes: 1

Views: 69

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Assuming assign_id doesn't have a ties, then you can use subquery :

select t.*
from table t
where assign_id = (select max(t1.assign_id)
                   from table t1
                   where t1.vehicle_id = t.vehicle_id
                   );

Upvotes: 1

jarlh
jarlh

Reputation: 44796

Have a sub-query that returns each vehicle_id's max assign_id. JOIN with that result:

select t1.*
from task t1
join (select vehicle_id, max(assign_id) assign_id
      from task
      group by vehicle_id) t2
  on t1.vehicle_id= t2.vehicle_id
  and t1.assign_id = t2.assign_id

Upvotes: 1

Related Questions