Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
ordered by name? – ypercubeᵀᴹ Dec 27 '12 at 12:12
@ypercube Yup that would do. – Kshitiz Sharma Dec 27 '12 at 12:25
See this similar question: Sorting the Table and getting the position. You only need the ROW_NUMBER "function". – ypercubeᵀᴹ Dec 27 '12 at 12:30
up vote 4 down vote accepted
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.

share|improve this answer
Thanks that works. – Kshitiz Sharma Dec 27 '12 at 12:39
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 posting your answer, you agree to the privacy policy and terms of service.

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