SQL query returning int column of relative order

Question

Say I have table with two columns: Name, Age.

I want to design a query that will return the names and ages sorted by age, but to also have an additional column which will run from 1 to N (the last row).

So for a table with the following rows:

John, 28
Jim, 30
Mike, 28

The following rows will be returned

John, 28, 1
Mike, 28, 2
Jim, 30, 3

How can I do this? (I'm using MySQL, btw, if it makes a diff).

Answer 1

Don't know about MySQL, but in SQL Server you do it this way:

SELECT Name, Age, ROW_NUMBER() OVER (ORDER BY AGE) AS RunningCount
FROM MyTable

A quick google search on "mysql and ROW_NUMBER()" I found this:

set @num = 0;
select *, @num := @num + 1 as row_number from TABLE

So presumably you could use that syntax instead

Answer 2

In mysql you can:

SELECT Row,Name,Age
FROM (SELECT @row := @row + 1 AS Row, Name,Age FROM table1 ) 
As derived1

However , this next approach is generic (but you need to have unique values in one of the columns of the table , in this example I've used name but it could be any unique column)

   select rank=count(*), a1.name, a1.age
   from table1 a1, table1 a2
   where a1.name >= a2.name
   group by a1.name , a1.age
   order by rank

Answer 3

If you're looking for a generic method that should work on any SQL platform, the following should work. I've never used it on a very large table, so I don't know how performance would be. Also, if you're name and age might be identical between two rows then you will get a duplicate number (for example, 1, 2, 3, 3, 5...). You can avoid that if you want to by adding the PK in there or some other set of columns that are guaranteed to be unique.

SELECT
     T1.name,
     T1.age,
     COUNT(T2.name) + 1
FROM
     My_Table T1
LEFT OUTER JOIN My_Table T2 ON
     T2.age < T1.age OR
     (
          T2.age = T1.age AND
          T2.name < T1.name
     )
GROUP BY
     T1.name,
     T2.age
ORDER BY
     T1.age,
     T2.name