February 11, 2010 at 3:30 am
declare @a table (id int)
insert into @a values (91)
insert into @a values (90)
insert into @a values (92)
insert into @a values (98)
insert into @a values (9)
insert into @a values (40)
insert into @a values (43)
insert into @a values (1)
insert into @a values (51)
insert into @a values (46)
insert into @a values (78)
insert into @a values (77)
insert into @a values (15)
insert into @a values (21)
Select top 5 id From @a Where id between 1 and 100 order by newid()
Select t.id from
(Select top 5 id From @a Where id between 1 and 100 order by newid() ) t order by t. id
my inner query gives me 5 values
and outer query should sort that 5 records in asc order but i am getting unexpected result
can anyone help me ?
in second query , the inner query's records are not same outer query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 11, 2010 at 3:39 am
NewID() function produces a new value each time you run it, so the first and second query shouldn’t produce the same records. If you need to produce the same records (the first time without any specific order and the second time with specific order), you’ll have to insert the records from the first query into a table variable (or temporary table), and then run 2 select queries on the table variable (one without order by and one with order by). Can you explain what you are trying to do and why you need to get the same records twice but each time with a different order?
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/
February 11, 2010 at 3:44 am
that is written in a historical stored proc and i have used the apporach u told me
but can i do it without using temptabl/table variable ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 11, 2010 at 4:14 am
I don’t think that you can do it in a different day, but the question is what are you trying to do that you need to get random records from a table twice – Once without a specific order and the second time those exact same records but with a specific order. Currently it is hard for me to believe that this is what you need, and I think that you posted a question with a simpler situation in order to get an answer. Maybe if you’ll write what you are trying to do, someone will come up with a better way to do it.
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/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply