I am using MSSQL Server 2005. In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results.
I am creating a functionality where I have to pick 5 customers randomly every time. Can anyone tell me how to create a query which will get random 5 rows (Id, and Name) every time when query is executed?
This is an old question, but attempting to apply a new field (either NEWID() or ORDER BY rand()) to a table with a large number of rows would be prohibitively expensive. If you have incremental, unique IDs (and do not have any holes) it will be more efficient to calculate the X # of IDs to be selected instead of applying a GUID or similar to every single row and then taking the top X # of.
If you wanted to select many more rows I would look into populating a #tempTable with an ID and a bunch of rand() values then using each rand() value to scale to the min-max values. That way you do not have to define all of the @randomId1...n parameters. I've included an example below using a cte to populate the initial table.
That said, everybody seems to come to this page for the more general answer to your question:
Selecting a random row in SQL
Select a random row with MySQL:
Select a random row with PostgreSQL:
Select a random row with Microsoft SQL Server:
Select a random row with IBM DB2
Select a random record with Oracle:
Select a random row with sqlite:
There is a nice Microsoft SQL Server 2005 specific solution here. Deals with the problem where you are working with a large result set (not the question I know).
Selecting Rows Randomly from a Large Table http://msdn.microsoft.com/en-us/library/cc441928.aspx