CODEWITHSUNDEEP
mysqlsql
larry hartman
larry hartman

Reputation: 143

SQL Selecting multiple columns based on max value in one column

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

Upvotes: 14

Views: 51933

Answers (6)

user2593417
user2593417

Reputation: 11

I wouldn't even use Max. Just combine GROUP BY and ORDER BY

SELECT * FROM orders GROUP BY item_number ORDER BY orderfileid DESC

then for minimum just change to ASC

Upvotes: 1

Vidyanand
Vidyanand

Reputation: 967

I think what you are looking for is the "Having" clause. Take a look at this.

select orderfileid, max(itemnumber), itemcost, warehouse from MyTable group by orderfileid having max(itemnumber) ;

Upvotes: -2

user747858
user747858

Reputation:

you can refer to a similar problem on how to group things using partitioning and picking one per partition in mysql

Deleting Rows: No Single Member Has More Than x Records

this is something similar to doing rank over in Oracle. my previous post was for oracle. my bad..

Upvotes: 0

Ricardo
Ricardo

Reputation: 173

Try

SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)

Upvotes: 0

Derek
Derek

Reputation: 23228

select 
    t.* 
from 
    table t 
    inner join (
        select itemnumber, max(orderfileid) maxof
        from table
        group by itemnumber
    ) m on t.itemnumber = m.itemnumber 
            and t.orderfileid = m.maxof

Upvotes: 4

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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

Upvotes: 17

Related Questions