InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 18:37:49 GMT20http://www.sqlservercentral.com/Forums/Topic608874-31-1.aspx[quote][b]dray (1/22/2009)[/b][hr]In the video example, wouldn't the same 20 people be selected, just returned in a different order? If you had a table with all the names, you would need to first have a sub query select all the records and add a uniqueidentifier (newguid()), then from that set select the top 20 and order by the uniqueidentifier.[/quote]When you have TOP and ORDER BY in the same statement, the ORDER BY is logically applied before the TOP.From the entry on TOP (http://msdn.microsoft.com/en-us/library/ms189463.aspx)[quote]If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.[/quote]Thu, 22 Jan 2009 09:15:19 GMTAdrian Hainshttp://www.sqlservercentral.com/Forums/Topic608874-31-1.aspxIn the video example, wouldn't the same 20 people be selected, just returned in a different order? If you had a table with all the names, you would need to first have a sub query select all the records and add a uniqueidentifier (newguid()), then from that set select the top 20 and order by the uniqueidentifier.Thu, 22 Jan 2009 08:33:04 GMTdrayhttp://www.sqlservercentral.com/Forums/Topic608874-31-1.aspxNice one ...:)Sat, 17 Jan 2009 03:59:24 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic608874-31-1.aspxOrdering by newid() is pretty lackluster in the performance department, I assumed this article would cover some great new ways to randomize data :(I've gotten by in a variety of cases by using row_number() over some indexed column(s), and predicating to some random offsets. This ends up being significantly better than the newid() approach, but the perf is still not that great if you have huge tables and some of your random offsets are deep into the table.The other main approach I've seen is using tablesample (i.e. http://www.mssqltips.com/tip.asp?tip=1308), this may work better on very large tables.For the common variety of requirement of fetching some random images in a website image gallery, I ended up creating a table to contain a randomized list of keys to the data rows. So if each image has an identity column as the PK, the randomized table contains a list of these ids in random order. You pay the perf to order by newid() one time with a nightly job, then each request simply chooses a random entry point into the randomized table and queries out the next N ids. Since the randomized table has contiguous ids it is cheap to seek into - where id is rand between 1 and max(id).Thu, 15 Jan 2009 08:51:13 GMTAdrian Hainshttp://www.sqlservercentral.com/Forums/Topic608874-31-1.aspxoooh, great tip, thanks.:)Thu, 15 Jan 2009 05:22:46 GMTIStevenChenhttp://www.sqlservercentral.com/Forums/Topic608874-31-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Video/65076/">Generating Random Results - SQL School Video</A>[/B]Wed, 26 Nov 2008 00:00:12 GMTAndy Warren