MySQL set a variable and run a query in the same statement/script

I'm have a data开发者_开发百科base customer name and date are the composite keys. I'm trying to pull out the 'x' largest or smallest transactions on each date and insert it into another table along with the ranking.

I'm using this code on individual runs, but it won't work when run thru JDBC from java for multiple runs.

set @N = 0;

SELECT @N := @N +1 AS rank, name, purchase FROM t1 ORDER BY close purchase
limit 15;

Any suggestions?

---

This works perfectly:

SELECT t.*, 
       @n := @n + 1 AS rank
  FROM WhateverTable t, (SELECT @n := 0) r

So your query will actually look like this:

SELECT @N := @N +1 AS rank, name, purchase 
FROM t1, (SELECT @N := 0) r 
ORDER BY purchase desc
limit 15;