Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Get a rowid in a SELECT statement

Posted on 2003-02-25
7
Medium Priority
?
378 Views
Last Modified: 2012-06-21
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?

0
Comment
Question by:thomasgeorge
7 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 8021572
In which database system ?
0
 
LVL 8

Expert Comment

by:Danielzt
ID: 8022160
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).



0
 

Expert Comment

by:shypan
ID: 8022466
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:thomasgeorge
ID: 8027316
I am using SQL Server. Is there a way to do this without a temporary table?
0
 

Author Comment

by:thomasgeorge
ID: 8027470
I am using SQL Server. Is there a way to do this without a temporary table?
0
 

Author Comment

by:thomasgeorge
ID: 8027476
I am using SQL Server. Is there a way to do this without a temporary table?
0
 

Accepted Solution

by:
Tyip earned 100 total points
ID: 8031123
No, the only solution is to use a temp table, but you can do this:

SELECT RowNum = IDENTITY(int, 1, 1), *
INTO NewTable
FROM OldTable
where ...
order by ...


select into will lock your tempdb until the transaction is complete. If this is a problem, then create your NewTable with RowNum int identity(1,1) and insert into NewTable select * from OldTable where ...

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question