Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Selecting multiple columns based on max value in one column

Tags:

sql

mysql

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

like image 708
larry hartman Avatar asked Jul 28 '11 14:07

larry hartman


People also ask

How do I select a maximum value from multiple columns in SQL?

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.

How do I get the maximum of one column in SQL?

The SQL MIN() and MAX() FunctionsThe MAX() function returns the largest value of the selected column.

Can we use Max on a column?

MAX can be used with numeric, character, and datetime columns, but not with bit columns. Aggregate functions and subqueries are not permitted.


1 Answers

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
like image 114
Joe Stefanelli Avatar answered Sep 25 '22 07:09

Joe Stefanelli