Login or Sign Up to become a member!

EXPERTS, INFORMATION, IDEAS & KNOWLEDGE

Social bookmarker Add this

Random Sorting

From Wiki

Jump to: navigation, search

Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code. SQL SERVER 2000 and up has a neat little function called NEWID() that can help you out.

First create this table and populate it

  1. CREATE TABLE #Test (ID INT,LastName VARCHAR(49))
  2.  
  3.     INSERT #Test VALUES(1,'Jackson')
  4.     INSERT #Test VALUES(2,'Douglas')
  5.     INSERT #Test VALUES(3,'Michael')
  6.     INSERT #Test VALUES(4,'Manson')
  7.     INSERT #Test VALUES(5,'Gabor')
  8.     INSERT #Test VALUES(6,'Welch')
  9.     INSERT #Test VALUES(7,'Anderson')
  10.     INSERT #Test VALUES(8,'Vader')


Run the code below from Query Analyzer/Management Studio and keep hitting F5. You will see that the results will be different every time.

  1. SELECT TOP 4 *
  2.     FROM #Test
  3.     ORDER BY NEWID()


Contributed by: --SQLDenis 02:52, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Handy tricks

175 Rating: 1.0/5 (2 votes cast)