sarode111 sarode111 - 1 year ago 63
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download