sarode111 sarode111 - 2 months ago 26
ASP.NET (C#) Question

How to use groupby,distinct in sql

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
from VoyageMaster
group by Vessel

Thank in Advance


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