thomasgeorge
asked on
Get a rowid in a SELECT statement
Is it possible to get a row index column in the result of a SELECT which always starts with 1 and increment upwards, irrespective of the parameters in WHERE and ORDER BY clauses?
shanesuebsahakarn
In which database system ?
if you are in oracle, you can use ROWNUM.
such as:
select rownum,ename,sal from emp
select rownum,ename,sal from emp where rownum<=5
select rownum,ename,sal from emp where rownum<=1
rownum start with 1, to the number of the records.
you can use it in WHERE(=1, <=) Order(you can use, does not make sense).
such as:
select rownum,ename,sal from emp
select rownum,ename,sal from emp where rownum<=5
select rownum,ename,sal from emp where rownum<=1
rownum start with 1, to the number of the records.
you can use it in WHERE(=1, <=) Order(you can use, does not make sense).
I am not sure which database you using, but if you are using Microsoft SQL you could use a cursor. Depending on the size of the table it could take a bit to run through the records. Basically you would create a cursor to run through all of the records and put into a temp table. In addition, you would have a variable that incriments by one for each record that it counts.
create newtable (put in fields and sizes, include one for the row number)
declare cursor_name scroll cursor
for select field1 from table_name
open cursor_name
declare @row_number numeric
delcare @field_name char(10)
set @row_number = 1
while @row_number <= @@cursor_rows
begin
fetch next from cursor_name into @field_date
insert into newtable values (@row_number, @field_name)
set @row_number = @row_number +1
end
close cursor_name
deallocate cursor_name
create newtable (put in fields and sizes, include one for the row number)
declare cursor_name scroll cursor
for select field1 from table_name
open cursor_name
declare @row_number numeric
delcare @field_name char(10)
set @row_number = 1
while @row_number <= @@cursor_rows
begin
fetch next from cursor_name into @field_date
insert into newtable values (@row_number, @field_name)
set @row_number = @row_number +1
end
close cursor_name
deallocate cursor_name
ASKER
I am using SQL Server. Is there a way to do this without a temporary table?
ASKER
I am using SQL Server. Is there a way to do this without a temporary table?
ASKER
I am using SQL Server. Is there a way to do this without a temporary table?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.