Generating Sequence Numbers

[progmr]

Does anyone know how to generate a sequence number in a query?

Example:

Select Last_name, first_name, (What to put here) Sequence from
test_table

Result:
Last_name First_name Sequence
Smith Sam 1
Frank Bob 2
Thanks!

 

[usatish]

Well,
There is no direct way of generating the sequence (i don't know if they got this option in V2R4)
However, you can achive it using "RANK" function

For example

Sel firt_name, last_name, Rank(<primary key of table>) From blah blah..

Satish

 

[BillDHS]

Be carful using RANK if the primary index of the table is not unique. RANK give the same rank to identical values:

INDEX RANK
a 1
b 2
b 2
c 4

You can also use CSUM(1,1) to add an ordered sequence to the rows without duplicates.

Select field1, field2, CSUM(1,1)
from db1
order by 1