January 11, 2009 at 11:42 pm
I have to write one query whivh will pick only odd rows. I hope, if I can identify each row in SQL server table, it will be simpler to me.
But, how SQL server identify each row? Like Oracle, it uses ROWNUM and ROWID.
What SQL server uses?
Thanks in advance.
January 12, 2009 at 1:25 am
You can identify rows with primary key or with another unique value in the table. What do you mean by saying odd records? Do you mean according to the records location (e.g. first record, second record, third… etc’)? If this is what you mean, then you should know that one of the table’s attributes is that it has no specific order. If you need to sample data and you want to get 50% of the data in the sample, there are other ways of doing so. For example you can use the row_number function and then get only the records that have odd number or you can order the records by newid function and get 50% from the records (notice that for big tables it won’t perform well). You’ll might get some suggestion to use the tablesample operator. Unfortunately according to books on line it isn’t really random so if you are thinking about it, you should read about it in BOL before making the decision.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2009 at 2:14 am
arup_kc (1/11/2009)
But, how SQL server identify each row? Like Oracle, it uses ROWNUM and ROWID.
SQL has no such functions. There's no documented way to get at the storage details of a row, and there's no need to do so. If you want to retrieve every other row, you need to define what ordering to use for that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 12, 2009 at 2:20 am
Sql Server also have ROW_NUMBER function, but it does not give you the storage order. You can use this function is select query by specifying the retrival ORDER.
For which purpose you need this function?
Regards,
Nitin
January 12, 2009 at 3:01 am
Frankly speaking, in one oracle interview , I have faced this question. Then I was starting to think to implement it in SQL server, but not getting any way.
I hope row_number() is my answer. It can identify all rows in a table. But to get even/odd records, I have tried the following query. But it is showing syntax error.
select user_id, name from users
where (row_number() OVER (order by user_id)) % 2 = 0
Any suggestion?
January 12, 2009 at 3:10 am
You can not use the row_number function in the where clause, so you need to work with common table expression that uses row_number function in the select list and then reference that column in the where clause of the query that is using the common table expression. Here is an example that is based on your post:
With MyCTE as (
select user_id, name, row_number() OVER (order by user_id) as RowNum
from users)
select user_id, name
from MyCTE
where RowNum % 2 = 0
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 12, 2009 at 3:20 am
arup_kc (1/12/2009)
select user_id, name from users
where (row_number() OVER (order by user_id)) % 2 = 0
Any suggestion?
You can use below query as well
select user_id, name from
(
select row_number() OVER (order by user_id) rowNum, user_id, name from users
) as tmpUser where rowNum % 2 = 0
Regards,
Nitin
January 13, 2009 at 6:07 am
Hi Adi and Nitin,
many thanks for the help. Now its working fine.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply