Given following query -

SELECT name, age FROM students WHERE age > 22

The result is -

name    age
----   ---
Kshitiz 23
Steve   22
Paul    24

How can I number the results sequentially? Like so -

serial  name    age
------  ----    ---
   1    Kshitiz 23
   2    Steve   22
   3    Paul    24

Please note that using surrogate key is not a solution. Because the surrogate id for record Kshitiz might be 9. I want whatever results are returned from whatever location to be numbered sequentially.

SELECT @a := @a + 1 serial, name, age
FROM students, (SELECT @a :=0) AS dummy
WHERE age > 22;

This works for MySQL. I was modifying from this example.

  • 6
    You should have an ORDER BY something, otherwise it's a more or less random ordering. – ypercubeᵀᴹ Dec 27 '12 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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