July 5, 2014 at 11:44 pm
Comments posted to this topic are about the item Random Sample
July 6, 2014 at 7:09 am
Thanks, a very good question.
Igor Micev,My blog: www.igormicev.com
July 6, 2014 at 7:12 am
It can be even used for random of N numbers of a table
like for e.g in this case:
SELECT TOP 2 *
FROM #Students
ORDER BY NEWID()
Igor Micev,My blog: www.igormicev.com
July 6, 2014 at 2:05 pm
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 7, 2014 at 12:40 am
Nice one
Thanks
July 7, 2014 at 1:13 am
I'm sure I'm missing something very basic here but I was under the impression that because there is no ordering automatically on a table, the row returned by TOP n is not ever guaranteed to be the same. Admittedly you could run a query 20000 times and get the same result but I didn't think run 20001 would necessarily produce the same value. Can somebody enlighten me please?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 7, 2014 at 2:43 am
Well.. I think you are partially right,
The fact there is no ORDER BY does not guarantee the records will be returned in order,
though that is not good enough to get random samples.
Most of the times the without ORDER BY select will return the same result sets on multiple invocation.
That is a nice and easy way to get samples, thank you to the author.
Iulian
July 7, 2014 at 3:03 am
Iulian -207023 (7/7/2014)
I'm sure I'm missing something very basic here but I was under the impression that because there is no ordering automatically on a table, the row returned by TOP n is not ever guaranteed to be the same. Admittedly you could run a query 20000 times and get the same result but I didn't think run 20001 would necessarily produce the same value. Can somebody enlighten me please?Well I think you are partially right,
The fact there is no order by does not quarantee the records will be returned in order.
But that is not good enough to get random samples.
I fully appreciate that it's not good enough for random sampling. Equally, sometimes the right answer can hinge on a very subtle nuance of SQL Server and, depending on interpretation, any one of the answers could be correct. In this case it's much more clearcut and I'm not disagreeing or nitpicking, I was just making sure that I hadn't misunderstood something fundamental.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
July 7, 2014 at 4:01 am
BWFC (7/7/2014)
I'm sure I'm missing something very basic here but I was under the impression that because there is no ordering automatically on a table, the row returned by TOP n is not ever guaranteed to be the same. Admittedly you could run a query 20000 times and get the same result but I didn't think run 20001 would necessarily produce the same value. Can somebody enlighten me please?
You are right. Option A, with no Order By, does (apparently) return row 1 every time, at least in SQL2014. But there is no guarantee, as the sequence is not defined. You might get a different result if you ran it 20001 times. It's quite possible (though unlikely) that SQL2016 will always return row 5. Or row 2. Or a random row.
July 7, 2014 at 6:54 am
initially I was under the impression that RAND(CAST(GETDATE() AS INT)) will generate a random order key.
But probably it will be random only if ran in different days, though if adjusting the random seed to be another one on each run, i.e. using seconds in the seed, that might be good enough to get random samples.
July 7, 2014 at 7:10 am
Nice straight forward question!
July 7, 2014 at 7:32 am
This was removed by the editor as SPAM
July 7, 2014 at 8:12 am
July 7, 2014 at 8:54 am
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply