CODEWITHSUNDEEP
mysqlsqlselectdistinctmax
GotaloveCode
GotaloveCode

Reputation: 1034

SQL query that can identify distinct values and maximum values

I need an sql statement to select distinct records and at the same time compare maximum or rather latest date from database.This is my table fleetschedule.It contains fleetschedule id,companyid,route,fleetnumber,traveldate. SQL query I tried:

SELECT DISTINCT fleetnumber and max(traveldate), routeid, companyid, traveldate
FROM fleetSchedule

problem is it gives just 1 value(the maximum). I require all distinct buses(identified by fleetnumbers) that travelled on the maximum/latest date. sample data of outcome i'm looking for:

fleetnumber routeid companyid   traveldate
MSH-17E     RT17    MSH         2009-07-26
FDH-17D     RT17    FDH         2009-07-26
MSH-17F     RT17    MSH         2009-07-25
MSH-27E     RT27    MSH         2009-08-24

sample of table am using:

fleetScheduleID companyID    routeID     travelDate fleetNumber
20                  MSH      RT17        2009-07-26     MSH-17E     
19                  MSH      RT17        2009-07-26     MSH-17D 
18                  MSH      RT27        2009-08-24     MSH-27E

Upvotes: 1

Views: 509

Answers (3)

Mikhail Vladimirov
Mikhail Vladimirov

Reputation: 13890

SELECT fleetNumber, 
       MAX(travelDate) AS travelDate, 
       SUBSTRING_INDEX(GROUP_CONCAT(routeID ORDER BY travelDate DESC), ',', 1) AS routeID, 
       SUBSTRING_INDEX(GROUP_CONCAT(companyID ORDER BY travelDate DESC), ',', 1) AS companyID
FROM fleetSchedule
GROUP BY fleetNumber;

Upvotes: 1

Paul McLean
Paul McLean

Reputation: 3550

select fleetnumber, routeid, companyid, max(traveldate)
from fleetSchedule
group by fleetnumber, routeid, companyid

To be clear, the above would give you each distinct bus and the latest date it traveled. If you would rather a list of distinct buses that have traveled on the latest date across the whole table, use the following

select distinct fleetnumber, routeid, companyid
from fleetSchedule
where traveldate = (select max(traveldate) from fleetSchedule)

Upvotes: 0

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

try this:

select * 
from fleetSchedule
where travelDate = ( select max(travelDate) from fleetSchedule )

Upvotes: 1

Related Questions