SQL to output line number in results of a query

Question

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.

Answer 1

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.

Answer 2

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)  FROM
(select distinct client_name from deliveries) SQ

will work.

Answer 3

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>