Hi All!

Is there any way to retrieve alternate rows from single query,without using any cursor or temptable .
There is rownum,rowid in Oracle,is there any equivalent in SQL Server 2000??

Thanks in Advance.



Cheers :)

vinod

__________________
Cheers :)

vinod

No, not in SQL 2000, not without a temp table, cursor, or table variable, unless the underlying result set already has some sort of integer identity, in which case you could approximate it.

In SQL 2005 you could by using the Row_Number() function.
For examples see
http://www.mutuallybeneficial.com/in...umber_2005.htm


David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com

I'm curious about your request. What would be the purpose of selecting alternate rows? What kind of result set would you be looking for?

Thanks,
Richard

Hii rstelma!!

Its a very nice link provided by you ,thanks Again !!

Its a legacy System which has data from two different soureces.
for every insert operation from SourceA , SourceB insert a row with some values.

The problem is that there is no unique/primary key in the table.But the Client ensures that first row is from source A, 2nd From Source B and so on.


Well its also an interview question i found for DBA !! :)



Cheers :)

vinod

Seems like you could add a bit column called [Sourc] and use the modulus function to turn the bit on from source A and off for source B. Then future queries would be direct based on the bit. Since there's no primary key you could add that column first then:

Update [table] set Source = 1 where [primary key] % 2 = 0

You can't rely on the physical order of the table if there is a clustered index on it.

Hii rstelma!!

We have no control over table(structure-add column)modifications,we can only query the table.
well we sort out using cursors and temp table.
Thanks again rstelma.




Cheers :)

vinod

Looks like you'll have to do it in your application.

I am not sure I understand your question. If you want to retireve every other row can't you simply select where your rowid is either odd or even?? Simple logic to build.