Fetch column data against group by data

Question

The source table structure is as defined in the image. I have fetched the data grouped by VesselID, with max function applied on cargodate(Date), as shown in the next image.

Now I need balance of the row fetched using group by. Please help.

What balance do you expect to get for a group? SUM() or the one associated with the date picked by the MAX()? — jkavalik, Nov 09 '15 at 11:40

score 0 · Answer 1 · answered Nov 09 '15 at 11:54

According to your SQL statement you always get a unique date from a group VesseleID since you are using MAX from group. By using join statement on date you can get your desired result.

Explanation:

-- Complete Data Set
1. Select * from DailyYardBalTable; 

-- Identical to your Query
2. SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable 
   WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID 

--Desired Result Set

3. SELECT T2.VesseleID,T2.CargoDate,T1.Balance FROM DailyYardBalTable T1
   INNER JOIN 
    (SELECT VesseleID, Max(CargoDate) as CargoDate FROM DailyYardBalTable
    WHERE CargoDate<'2015-11-10 16:47:49.493' GROUP BY VesseleID) T2
  ON T1.CargoDate=T2.CargoDate GROUP BY T2.VesseleID,T2.CargoDate,T1.Balance

Here is the data set respective to the SQL statement #1, #2, #3.

Fetch column data against group by data

1 Answers1