sarode111 sarode111 - 12 days ago 5
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

Answer

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