Get value from last row in group

Question

I need to group data by Truck and find the # of delivery stops per Truck and the U.S. state of the last stop.

Raw data looks like this:

Truck | Sequence of Stops | State

A 1 TX
A 2 MO
A 3 KS
BB 1 AR
BB 2 IN

Final results need to look like this.

Truck | # of Stops | State of last stop

A 3 KS
BB 2 IN

Using this sql, I can determine # of Stops. Determining the state of last stop is what I'm having issues with. Some suggestions are to use Row_number but that seems a bit complicated for what I need.

SELECT t.Carrier as 'Truck' , 
    MAX(DeliverySeqNbr) AS 'Sequence of Stops'
FROM Orders o
INNER JOIN Trucks t ON t.TruckID = o.TruckID
GROUP by t.Carrier

Any help is appreciated. Thank you.

Answer 1

Try

;With cte As
(SELECT t.Carrier as 'Truck Carrier' , SUM(o.TotalFreight) as 'Total Freight', SUM(o.JobSiteCharge) as 'Job Site Charge', SUM(o.TotalCubes) as 'Total Cubes',
    MAX(o.DeliverySeqNbr) AS 'Nbr of Stops', t.TruckID
FROM Orders o
INNER JOIN Trucks t ON t.TruckID = o.TruckID
WHERE
    o.Closed = 0 AND 
    o.DeletedOrder = 0 AND 
    o.OrderID NOT LIKE 'T%' AND 
    t.Carrier LIKE'K[0-9]%'
GROUP by t.Carrier, t.truckid)
Select c.[Truck Carrier], RIGHT([Truck Carrier], 3), c.[Total Freight], c.[Job Site Charge], c.[Total Cubes],
   c.[Nbr of Stops], s.State
FROM cte c
CROSS APPLY(Select o1.State From Orders o1 Where c.TruckID = o1.TruckID And c.[Nbr of Stops] = o1.DeliverySeqNbr) s;

Tom

Answer 2

Using the Row_Number() is the best and most efficient way to do this.

;With cte As
(Select t.Carrier, DeliverySeqNbr, State,
  Row_Number() Over(Partition By t.Carrier Order By DeliverySeqNbr Desc) As rn
From Orders o
Inner Join Truck t On t.TruckID = o.TruckID)
Select Carrier As 'Truck', DeliverySeqNbr As 'Last Stop', State
From cte
Where rn = 1;

Tom

Answer 3

Hi @EM ，

Please also check:

;WITH cte  
as( SELECT t.Carrier as 'Truck' ,   
     MAX(DeliverySeqNbr) AS 'Sequence of Stops'  
    FROM Orders o  
    INNER JOIN Trucks t ON t.TruckID = o.TruckID  
    GROUP by t.Carrier)  
  
SELECT o.*  
FROM Orders o  
JOIN cte c ON o.Truck=c.Truck   
AND o.[Sequence of Stops]= c.[Sequence of Stops]  
ORDER BY o.Truck  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 4

(using pituach's suggestion for posting comment)

Thank you both for your suggestions. My requirements for this data has changed. Now totals of various columns from Orders are required. This SQL that gives me everything but the state of last stop:

SELECT t.Carrier as 'Truck Carrier' , RIGHT(t.Carrier, 3), SUM(o.TotalFreight) as 'Total Freight', SUM(o.JobSiteCharge) as 'Job Site Charge', SUM(o.TotalCubes) as 'Total Cubes',
    MAX(DeliverySeqNbr) AS 'Nbr of Stops'
FROM Orders o
INNER JOIN Trucks ON t.TruckID = o.TruckID
WHERE
    o.Closed = 0 AND 
    o.DeletedOrder = 0 AND 
    o.OrderID NOT LIKE 'T%' AND 
    t.Carrier LIKE'K[0-9]%'
GROUP by t.Carrier, RIGHT(t.Carrier, 3)

The state of last stop needs to be added to this sql. I tried joining the cte. This SQL has numerous errors but hopefully you can see what I'm trying to do.

SELECT  t.Carrier as 'Truck Carrier' , RIGHT(t.Carrier, 3), SUM(o.TotalFreight) as 'Total Freight', SUM(o.JobSiteCharge) as 'Job Site Charge', SUM(o.TotalCubes) as 'Total Cubes',
    MAX(DeliverySeqNbr) AS 'Nbr of Stops',
    (With cte As
         (Select t.Carrier, DeliverySeqNbr, ShipState,
           Row_Number() Over(Partition By t.Carrier Order By DeliverySeqNbr Desc) As rn
         From Orders o
         Inner Join Trucks t On t.TruckID = o.TruckID
         WHERE
            o.Closed = 0 AND 
            o.DeletedOrder = 0 AND 
            o.OrderID NOT LIKE 'T%' AND 
            t.Carrier LIKE'K[0-9]%'
        )
         Select ShipState
         From cte
         Where rn = 1)
FROM Orders o
INNER JOIN Trucks t ON t.TruckID = o.TruckID
WHERE
    o.Closed = 0 AND 
    o.DeletedOrder = 0 AND 
    o.OrderID NOT LIKE 'T%' AND 
    t.Carrier LIKE'K[0-9]%'
GROUP by t.Carrier, RIGHT(t.Carrier, 3)