Q:

MySQL: Get nth highest value for each group in a table

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. 

A:

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;

