How can I construct a query to show an auto-incremented number?

Question

I have a query, select * from data, which results in:

Name    State  
a       malaysia  
b       Indonesia

I want this:

No      Name    State  
1       a       malaysia  
2       b       Indonesia

How might I do this?

http://stackoverflow.com/questions/6294382/access-sql-how-to-make-an-increment-in-select-query — mcalex, Mar 27 '13 at 01:52

score 3 · Accepted Answer · answered Mar 27 '13 at 01:53

3

SELECT Name
, State
, RANK() OVER (ORDER BY Name, State) as Ranking
FROM Data

answered Mar 27 '13 at 01:53

Vinnie

3,889
1
26
29

how to rank but query use alias (as)? – rian Mar 27 '13 at 02:18
Right, that was to give the Rank() column a name. You don't need the "as", but it's proper. – Vinnie Mar 27 '13 at 02:50

score 0 · Answer 2 · answered Mar 27 '13 at 01:55

I don't know if there's a database-agnostic way.

Oracle allows you to state the rownum (http://www.adp-gmbh.ch/ora/sql/rownum.html) as a column to return.

H2 supports this syntax as well

The answer might depend on the flavor of database you are using.

score 0 · Answer 3 · answered Mar 27 '13 at 02:03

Since you don't say in what database lets just make it PostgreSQL:

select 0+row_number() OVER(ORDER BY name DESC) as position, * from test.users ORDER BY name

This might be different in other databases (it will for sure). The idea should be the same thought, so this way you know how to do in one. Should be easy to do it in some other.

How can I construct a query to show an auto-incremented number?

3 Answers3