Get value from last row in group

EM 271 Reputation points
2021-09-27T15:59:36.877+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,599 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-09-29T03:37:18.033+00:00

    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


3 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2021-09-27T16:17:50.627+00:00

    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


  2. EchoLiu-MSFT 14,581 Reputation points
    2021-09-28T01:31:39.493+00:00

    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.

    0 comments No comments

  3. EM 271 Reputation points
    2021-09-28T16:17:08.707+00:00

    (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)
    
    0 comments No comments