Microsoft SQL Server - How to select several random records from a table.
Sometimes it is necessary to select several random records from a database.
This task can be achieved by using built-in function NEWID() using TOP clause and ORDER BY clause.
Suppose you have a table SoftFernNews from which you want to select 10 random rows containing Titles and IDs every time when user visits your home page.
Solution.
The stored procedure looks as following:
ALTER PROCEDURE dbo.SelectRandom
AS SELECT TOP 10 NewsID, NewsTitle
FROM dbo.SoftFernNews
ORDER BY NEWID()
After executing the stored procedure, the result was:
First time:
NewsID NewsTitle
162 Al-Qaeda's number two leader, was killed in Pakistan on 22 August.
203 Rock in Rio festival
100 Inspection of electronics shops for fake Apple Stores
107 Birds made from amethyst, onyx....
170 British massively stocked with Baseball Bats, Police Telescopic Tonfa etc.
85 Kinect for Windows SDK
184 About Blizzard Entertainment - I
113 Mike Tyson - a nurse.
250 Fantastic vertical gardens, part II
217 Skype is officially now part of Microsoft
Second time:
NewsID NewsTitle
107 Birds made from amethyst, onyx....
207 RWC 2011, the latest results and the Quarter Finals
240 Toyota robots – domestic robot
296 Muhammad Ali was taken to hospital
110 Amazing ads
221 Microsoft’s Skype Deal: Winners and Losers
128 Beautiful landscapes part III.
144 Why Google’s purchased Motorola Mobility?
279 Horrifying tricks from extreme artist.
159 Street Art – incredible illusions. Part I
Third time:
NewsID NewsTitle
161 Martial art champion ‘Black Tiger’ strikes to death.
96 Corrida - some photos
240 Toyota robots – domestic robot
242 More about robots – II
287 iPhone 4S expenses
148 Go swimming at the Auckland during winter time. Part II
193 Hollywood stars tattoos
184 About Blizzard Entertainment - I
86 Fake Chinese Apple store
293 Euro 2012 draw occurred
As you can see, every time it selected some random records.
More similar topics with SQL tips:
Oracle database - How to select several random records from a table
Database DB2 - How to select several random records from a table
MySQL database - How to select several random records from a table
PostgreSQL - How to select several random records from a table
Microsoft SQL Server - How to select several random records from a table