|
Subject:
|
Alternate Rows with no primary key
|
|
Posted By:
|
vinod_yadav1919
|
Post Date:
|
3/27/2006 8:54:16 AM
|
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
|
|
Reply By:
|
David_the_DBA
|
Reply Date:
|
3/27/2006 7:02:39 PM
|
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/index_files/RowNumber_2005.htm
David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
|
|
Reply By:
|
rstelma
|
Reply Date:
|
3/29/2006 1:39:57 PM
|
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
|
|
Reply By:
|
vinod_yadav1919
|
Reply Date:
|
3/30/2006 8:31:24 AM
|
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
|
|
Reply By:
|
rstelma
|
Reply Date:
|
3/30/2006 11:24:28 AM
|
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
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
3/31/2006 12:51:30 AM
|
You can't rely on the physical order of the table if there is a clustered index on it.
|
|
Reply By:
|
vinod_yadav1919
|
Reply Date:
|
3/31/2006 2:58:57 AM
|
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
|
|
Reply By:
|
rstelma
|
Reply Date:
|
3/31/2006 11:00:41 AM
|
Looks like you'll have to do it in your application.
|
|
Reply By:
|
robprell
|
Reply Date:
|
4/5/2006 6:10:49 PM
|
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.
|
