Grouping and retrieving most recent entry in a table for each group
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
1234开发者_C百科56 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
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.
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)".
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.
精彩评论