Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

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
6  
You should have an ORDER BY something, otherwise it's a more or less random ordering. –  ypercube Dec 27 '12 at 12:42
add comment

Your Answer

 
discard

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.