28

I would like to generate a line number for each line in the results of a sql query. How is it possible to do that?

Example: In the request 

select distinct client_name from deliveries

I would like to add a column showing the line number

I am working on sql server 2005.

madewulf
  • 1,870
  • 4
  • 26
  • 41
  • What SQL engine are you using? – pjp Aug 18 '09 at 11:56
  • What database platform are you using? MySQL? SQL Server? Oracle? From what programming language/environment are you running the query? Is there a particular reason you consider it preferable to do this via query and not via program code? – VoteyDisciple Aug 18 '09 at 11:56
  • I use microsoft reporting services as the environment, and I need an integer identifier for my rows because of a bug in this tool. – madewulf Aug 18 '09 at 11:58
  • 1
    How is this line number useful? If you rerun the query then it can change. Wouldn't you be better off adding an IDENTITY column on the client? – pjp Aug 18 '09 at 11:59
  • No, i just need this for presentation purpose, and I cannot add an identity column because the values are coming from request containing the distinct keyword (I hope I understood your question well). – madewulf Aug 18 '09 at 12:01
  • But as the source tabel changes, so might the line number used for the row containing a particular record i.e. "Hey Joe, record on line number 12, named cheese", "Uh Bill, I see a record named wine on line number 12" – Russ Cam Aug 18 '09 at 12:02
  • 2
    @Russ Cam - so your solution is nobody should print out any report, ever? – APC Aug 18 '09 at 12:08

5 Answers5

40

It depends on the database you are using. One option that works for SQL Server, Oracle and MySQL:

SELECT ROW_NUMBER() OVER (ORDER BY SomeField) AS Row, *
FROM SomeTable

Change SomeField and SomeTable is according to your specific table and relevant field to order by. It is preferred that SomeField is unique in the context of the query, naturally.

In your case the query would be as follows (Faiz crafted such a query first):

SELECT ROW_NUMBER() OVER (ORDER BY client_name) AS row_number, client_name
FROM (SELECT DISTINCT client_name FROM deliveries) TempTable

I think it won't work for SQLite (if someone can correct me here I would be grateful), I'm not sure what's the alternative there.

Roee Adler
  • 33,434
  • 32
  • 105
  • 133
4

You can use the ROW_NUMBER function for this. Check the syntax for this here http://msdn.microsoft.com/en-us/library/ms186734.aspx

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY FirstName) AS 'Row#'    
FROM Sales.vSalesPerson;

For your query,

SELECT client_name, ROW_NUMBER() Over (Order By client_name) AS row_number 
FROM   (select distinct client_name from deliveries) SQ

will work.

Faiz
  • 5,331
  • 10
  • 45
  • 57
1

In Oracle

SQL> select row_number() over (order by deptno) as rn
  2         , deptno
  3  from
  4     ( select distinct deptno
  5            from emp
  6          )
  7  /

        RN     DEPTNO
---------- ----------
         1         10
         2         20
         3         30

SQL>
APC
  • 144,005
  • 19
  • 170
  • 281
0

OR you could also do

SELECT DISTINCT client_name, @num := @num + 1 AS lineNum(this is your new col)
FROM deliveries
JOIN (SELECT @num :=0) AS n ON 1=1;

hope this helps too :)

Nilesh Thakkar
  • 2,877
  • 1
  • 24
  • 43
giogio ben
  • 51
  • 5
0

In SQL we are also using this query:

select row_number() over (order by table_kid) as S_No ,table_columnname,table_columnname2 from tablename where table_fieldcondition='condtionnal falg or data ';

Here table_kid or may be other table_columnname

Undo
  • 25,519
  • 37
  • 106
  • 129
user7912960
  • 21
  • 2