How to enumerate returned rows in SQL?

Question

I was wondering if it would be possible to enumerate returned rows. Not according to any column content but just yielding a sequential integer index. E.g.

select ?, count(*) as usercount from users group by age

would return something along the lines:

1    12
2    78
3     4
4    42

it is for https://data.stackexchange.com/

Answer 1

try:

SELECT
    ROW_NUMBER() OVER(ORDER BY age) AS RowNumber
        ,count(*) as usercount 
    from users 
    group by age

Answer 2

If it's Oracle, use rownum.

SELECT SOMETABLE.*, ROWNUM RN
FROM SOMETABLE
WHERE SOMETABLE.SOMECOLUMN = :SOMEVALUE
ORDER BY SOMETABLE.SOMEOTHERCOLUMN;

The final answer will entirely depend on what database you're using.

Answer 3

For MySql:

SELECT  @row := @row + 1 as row FROM anytable a, (SELECT @row := 0) r

Answer 4

use rownumber function available in sql server

SELECT 
    ROW_NUMBER() OVER (ORDER BY columnNAME) AS 'RowNumber',count(*) as usercount
    FROM users

Answer 5

How you'd do that depends on your database server. In SQL Server, you could use row_number():

select  row_number() over (order by age)
,       age
,       count(*) as usercount 
from    users 
group by 
        age
order by
        age

But it's often easier and faster to use client side row numbers.

Answer 6

for Mysql

set @row:=0;

select @row:=@row+1 as row, a.* from table_name as a;

Answer 7

In contrast to majority of other answers, and in accordance of the actual OP question, to

enumerate returned rows (...) NOT according to any column content

but rather in the order of returned query, one could use a dummy ordinal variable to ORDER BY in an ROW_NUMBER function, e.g.

ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS row_num

where one could actually use anything as an argument to the SELECT-statement, like SELECT 100, SELECT ‘A’, SELECT NULL, etc..

This way, the row numbers will be enumerated in the same order the data was added to the table.