Creating a counter to work as an ID Number

[Alexrp25]

Is there way in which I write a select code, I can create a field in which works as a counter, so that I can have a kind of ID for the line record?

for instance, I have table like the below:

Month Value
Jan 10000
Fev 10000
Mar 15000
Abr 10500

and I want the results below:

Month Value ID
Jan 10000 1
Fev 10000 2
Mar 15000 3
Abr 10500 4

Is there a way to do this?


Thanks in advance

Alex.

 

[swampBoogie]

It can be done if the result can be distinctly ordered

select month,value,
(select count(*) from t
where orderColumn <= tc.orderColumn) as id
from t as tc
order by orderColumn

 

[BillDHS]

Teradata has a function called CSUM that gives a running total for a numeric field. When CSUM(1,1) is used as a calculated field, the column starts at 1 and adds 1 to each subsequent row value.

There is also a function called RANK that adds a rank value to the numeric value, but this includes a value order that you may not want, and identical values get the same rank.

 

[rrrkrishnan]

If you are not bothered about any order to assign the id, you can use rownum too.
eg : select month,value,rownum from tablename;

 

[swampBoogie]

rownum is not an ANSI SQL feature.

 

[rrrkrishnan]

You are right, I thought this question was from the Oracle 8i forum. Sorry about that.

 

[dnoeth]

Rownum is not ANSI, but ROW_NUMBER is SQL:1999.

Teradata's CSUM was implemented before there was SQL:1999, but now it's also using standard compliant SQL. Btw, never use CSUM(1,1) as this will result in a totally skewed spool file. Use CSUM(1, any real column to order by) in V2R3
or
SUM(1) OVER (ORDER BY ....) in V2R4
or
ROW_NUMBER () OVER (ORDER BY ...) in V2R5

Dieter