CODEWITHSUNDEEP
sqloracle-database
David Collins
David Collins

Reputation: 19

Oracle - add counter to query

I am looking to add in a simple counter to an SQL Query - i.e., if I run a query on individual surnames that returns 3 results I would like the results to display their row value from the query. E.g.:

Surnames   Counter
Smith         1
Murphy        2
Brown         3

How can this be done?

Upvotes: 0

Views: 10017

Answers (5)

Ruben
Ruben

Reputation: 1

Cerate an SQL Expression Field named something like 'RowNumber' with simply 'rownum' as the statement. Add that field to where you want it in the details row, then sort your report by the name of your Expression Field, 'RowNumber' in this case.

Upvotes: 0

Spider
Spider

Reputation: 387

SELECT Surnames,row_number() OVER (ORDER BY Surnames) as Counter FROM table;

Upvotes: 0

Mureinik
Mureinik

Reputation: 312289

The row_number analyitic function should do the trick:

SELECT surname, ROW_NUMBER() OVER (ORDER BY surname) AS counter
FROM   my_table

EDIT:
In a simple query like this, you could just use the rownum pseudocolumn:

SELECT   surname, rownum
FROM     my_table
ORDER BY 1 DESC

Upvotes: 3

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8517

You can try this one

SELECT Surnames,row_number() OVER (ORDER BY Surnames)
FROM t

SQL Fiddle:- http://sqlfiddle.com/#!4/ad3aa/3

Upvotes: 0

davek
davek

Reputation: 22925

select a.*
, rownum rnum
from 
(select surname from name_table order by surname) a

will get you a simple numbering (according to the order of surname, but will not deal with ties.

Upvotes: 1

Related Questions