mysql获取表中一列第n高的值_MySQL：获取表中每个组的第n个最大值

Disclaimer: I'm pretty new to this so I apologize in advance if I use incorrect terminology - I'll be happy to clarify if anything isn't clear.

Say I have a table like this, that tracks all orders by the store location and department:

STORE DEPT ORDER AMOUNT

--------------------------------------------------

NYC Clothing P00001 $30

NYC Clothing P00002 $25

NYC Clothing P00003 $40

... ... ... ...

NYC Housewares P00011 $140

NYC Housewares P00012 $125

NYC Housewares P00013 $140

... ... ... ...

CHI Clothing P00021 $30

CHI Clothing P00022 $20

CHI Clothing P00023 $20

... ... ... ...

CHI Housewares P00031 $180

CHI Housewares P00032 $110

CHI Housewares P00033 $125

... ... ... ...

If I wanted the highest sale in each department, in each store, I think that's just

SELECT Store, Dept, max(Amount)

FROM mytable

GROUP BY Store, Dept

But what if I want the 2nd highest sale in each department? In other words, I want a query that produces the following results from the above table:

STORE DEPT ORDER AMOUNT

--------------------------------------------------

NYC Clothing P00001 $30

NYC Housewares P00013 $140

CHI Clothing P00022 $20

CHI Housewares P00033 $125

Note that I don't necessarily want the second highest UNIQUE amount - if there's a tie for the top value, as there is in NYC-Housewares, I need that value returned (not $125).

In SQL Server I've learned I can use PARTITION BY to order within the groups and then select the rank that I want, but it doesn't appear that the same syntax works for MySQL. Similar questions I've found on the web involve using LIMIT but I haven't found a solution that works for my situation.

Any help would be greatly appreciated.

解决方案

I hope there's a better way, but you can get this by intersecting or not-ing two subqueries:

Select mytable.Store, mytable.Dept, mytable.Order, mytable.Amount

from mytable m

inner join

(Select Amount from mytable n where m.store = n.store and m.dept = n.dept order by Amount desc limit 2) as high_enough

on mytable.Amount = high_enough.Amount

left join

(Select Amount from mytable n where m.store = n.store and m.dept = n.dept order by Amount desc limit 2) as too_high

where too_high.Amount is null

group by Store, Dept;