14

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

larry hartman
  • 143
  • 1
  • 1
  • 4
  • something among the lines of: `select distinct item_number, orderfileid, item_cost, warehouse from orders order by item_number, orderfileid desc` perhaps? – PtPazuzu Jul 28 '11 at 14:39

6 Answers6

17

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
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • Thank you. In trying this solution i am getting different results. Perhaps I'm typing something wrong but I get only the value with a unique entry I get the results for the item number 6789 and not the others. Of course Im not using this simplified data set i have in the example, so I may be losing it in translation – larry hartman Jul 28 '11 at 17:04
  • @larry: I've confirmed that the query I've written returns the expected results for the sample data set you provided. – Joe Stefanelli Jul 28 '11 at 17:24
  • Thanks I'll continue tweaking and let you know results. Thanks for getting me to next step. – larry hartman Jul 28 '11 at 17:29
  • It now works. My example was too simple, I had a where filldate clause after your statement above and once I also included it in the select from the inner join everything works perfect. Great Help!!1 – larry hartman Jul 28 '11 at 17:54
4
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
Derek
  • 21,828
  • 7
  • 53
  • 61
1

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

user2593417
  • 11
  • 1
  • 2
    No because OP only wants 1 of each item, not all instances of the item ordered by the orderfield. NOTE: I am justifying the Necro, because the answer is a Necro – Tom Heard Jul 03 '14 at 04:31
0

Try

SELECT * FROM `TABLE` WHERE orderfileid=(select max(orderfileid) from TABLE)
Ricardo
  • 173
  • 7
0

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..

Community
  • 1
  • 1
-2

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) ;

Vidyanand
  • 967
  • 8
  • 14