2

How might one increment a "fixed" value in the SELECT clause of a query, so that each row has a different value set? For an obviously not working example that illustrates the intention:

SELECT
    name, ++x as ordinal
FROM
    foo;

name   | ordinal
----------------
john   | 0
paul   | 1
george | 2
ringo  | 3

The ultimate intention is to select users from a database to win first, second, and third prizes. I would have one user win first prize, five second prize winners, and twenty third prize winners:

SELECT
    name, IF(++x<2, 'first',  IF(x<7, 'second', 'third') ) as prize
FROM
    foo LIMIT 26;

name     | prize
------------------
alice    | first
bob      | second
charlie  | second
dotan    | second
erik     | second
frank    | second
george   | third
henry    | third
:        | :
:        | :

This is running on MySQL v5.5. Thank you.

1 Answer 1

4

You can use user-defined variables for this.

In your first example this would be as simple as this:

SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY whatever_column;

Here we initialize the variable in another query. This is often more comfortable than doing this:

SET @rn := -1;
SELECT
name, @rn := @rn + 1 AS ordinal
FROM
your_table
ORDER BY whatever_column;

when you're not allowed to execute multiple statements in PHP or whatever.
The variables are session bound!
Further we initialize it here with -1 so that the first row will have value 0, because @rn + 1 is evaluated first.
Also note, that I added an ORDER BY clause. If you don't add it, your result is kind of meaningless, cause there's no order in a relational database unless you specify it. And the calculations done with any variables in the SELECT clause rely on the order of the rows.

Regarding your second example, here's just important, to take care of the order in the SELECT clause.

SELECT
name, @rn := @rn + 1 AS ordinal,
IF(@rn < 2, 'first', IF(@rn < 7, 'second', 'third')) AS prize
FROM
your_table
CROSS JOIN (SELECT @rn := -1) AS var_init_subquery
ORDER BY points DESC; /*or whatever_column*/
2
  • This is very useful and I learned much. Thank you!
    – dotancohen
    Jun 15, 2016 at 14:44
  • EDIT: I don't know why it came to my mind so late^^, but there's no need to assign the IF(...) to another variable. Don't know, why I did this in the first place.
    – tombom
    Jun 20, 2016 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.