OK so I have looked theough the other solutions an no help. So here is what I am trying to do. I need to select the row with multiple columns where the value in one column is the max value.
here is sample data
orderfileid item number item cost warehouse
1 1234 3.45 ATL
1 2345 1.67 DFW
3 2345 2.45 NYY
3 678 2.4 ORD
2 1234 1.67 DFW
I need to select the entire row where the orderfileid is the max for each unique item number
the returned dataset should look like
orderfileid item number item cost warehouse
2 1234 1.67 DFW
3 2345 2.45 NYY
3 6789 2.4 ORD
I think i tried every combination of select max(orderfileid) i can think of
Any help would be appriciated. thanks
The MySQL Solution If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST: GREATEST(value1,value2,...) Given two or more arguments, it returns the largest (maximum-valued) argument.
The SQL MIN() and MAX() FunctionsThe MAX() function returns the largest value of the selected column.
MAX can be used with numeric, character, and datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.
You need to find your MAX values in a subquery, then use those results to join to your main table to retrieve the columns.
SELECT t.OrderFileId, t.ItemNumber, t.ItemCost, t.Warehouse
FROM YourTable t
INNER JOIN (SELECT ItemNumber, MAX(OrderFileId) AS MaxOrderId
FROM YourTable
GROUP BY ItemNumber) q
ON t.ItemNumber = q.ItemNumber
AND t.OrderFileId = q.MaxOrderId
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With