Grouping and retrieving most recent entry in a table for each group

Question

First off, please bear with me if I don't state the SQL question correctly.

I have a table that has multiple columns of data. The selection criteria for my table groups based on column 1(order #). There could be multiple items on each order, but the item #'s are not grouped together.

Example:

Order     Customer     Order Date     Order Time     Item     Quantity  
123456    45           01/02/2010     08:00          140       4  
123456    45           01/02/2010     08:30          270      29  
123456    45           03/03/2010     09:00          140       6  
123456    45           04/02/2010     09:30          140      10  
123456    45           04/02/2010     10:00          270      35  

What I need is a result like:

Order     Customer     Order Date     Order Time     Item     Quantity  
123456    45           04/02/2010     09:30          140       10  
123456    45           04/02/2010     10:00          270       35  

This result shows that after all the changes the final order includes 10 of Item 140 and 35 of Item 270.

Is this possible.

python

Answer 1

Since you did not specify which database product or version, I'll show a solution that would work in SQL Server 2005 or higher:

With RankedItems As
 (
 Select Order, Customer, [Order Date], [Order Time], Item, Quantity
  , Row_Number() Over( Partition By Order, Customer, Item Order By [Order Date] Desc, [Order Time] Desc ) As Num
 From Table
 )
Select Order, Customer, [Order Date], [Order Time], Item, Quantity
From RankedItems
Where Num = 1

Here is a more database-agnostic solution:

Select T.Order, T.Customer, T.[Order Date], T.[Order Time], T.Item, T.Quantity
From Table As T
Where T1.[Order Date] = (
      Select Max(T1.[Order Date])
      From Table As T1
      Where T1.Order = T.Order
       And T1.Customer = T.Customer
       And T1.Item = T.Item
      )
 And T1.[Order Time] = (
       Select Max(T1.[Order Time])
       From Table As T1
       Where T1.Order = T.Order
        And T1.Customer = T.Customer
        And T1.Item = T.Item
        And T1.[Order Date] = T.[Order Date]
       )

The catch in this later solution is that if there are multiple rows with the same Order, Customer, Item, Order Date and Order Time, you will get multiple rows in the above output.

Answer 2

Since you didn't mention it, I'll assume you're using Oracle:

SELECT ORDER, CUSTOMER, ITEM, MAX(ORDER_TIMESTAMP), MAX(QUANTITY)
  FROM (SELECT ORDER,
               CUSTOMER,
               ITEM,
               TO_DATE(TO_CHAR(ORDER_DATE, 'YYYY-MM-DD') || ' ' ||
                       TO_CHAR(ORDER_TIME, 'HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS')
                 AS ORDER_TIMESTAMP,
               QUANTITY
          FROM MY_TABLE)
  GROUP BY ORDER, CUSTOMER, ITEM;

Share and enjoy.

Answer 3

Bob's answer looks good, but from reading the query it looks like what is wanted is the maximum of the quantity column rather than the sum, which would mean changing the "SUM(QUANTITY)" aggregate expression to "MAX(QUANTITY)".