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

Question

I'm have a database 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?

define it won't work? errors? incorrect behavior? does it work for the first run and fail on successive runs? or all runs? — mellamokb
– mellamokb, Commented Oct 7, 2011 at 16:00

Adriano Carneiro · Accepted Answer · 2011-10-07 16:16:38Z

5

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;

edited Oct 7, 2011 at 16:16

answered Oct 7, 2011 at 16:11

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related