This is the table data
Table name is voyagemaster. How to select distinct Vessel and also one Vessel have lots of voyagenumber, hence I am adding the voyagedate
for select the voyagenumber as max(voyagedate).
According to vessel select max(voyagedate) and voyagenumber.
How to solve..
select distinct(Vessel), VoyageID, VoyageNumber, max(VoyageDate) as vdate, VIA,
ROTNo, CutOffDate, CutOffTime, ETA, ETD
group by Vessel
Try This :
SELECT A.Vessel,V1.VoyageID,V1.VoyageNumber,A.VoyageDate FROM VoyageMaster V1 JOIN ( SELECT Vessel,MAX(VoyageDate) VoyageDate FROM VoyageMaster GROUP BY Vessel ) A ON A.Vessel = V1.Vessel AND V1.VoyageDate = A.VoyageDate