CODEWITHSUNDEEP
sqlenumeration
SilentGhost
SilentGhost

Reputation: 319601

How to enumerate returned rows in SQL?

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/

Upvotes: 24

Views: 45765

Answers (7)

Alaroff
Alaroff

Reputation: 2298

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.

Upvotes: 1

srbcheema1
srbcheema1

Reputation: 610

for Mysql

set @row:=0;

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

Upvotes: 0

FrustratedWithFormsDesigner
FrustratedWithFormsDesigner

Reputation: 27486

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.

Upvotes: 6

Pranay Rana
Pranay Rana

Reputation: 176896

use rownumber function available in sql server 

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

Upvotes: 2

BenV
BenV

Reputation: 12452

For MySql:

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

Upvotes: 4

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 1

KM.
KM.

Reputation: 103587

try:

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

Upvotes: 25

Related Questions