Showing random records from database

Thanks Pawan,

Yes, that is what I have found when searching online. But a lot of comments out there suggest that this method is only good for small tables and not a good idea for larger tables. Do you have an opinion on that?

Thanks

Your table size? I mean number of rows in your table.

Well, it's tiny at the moment. But I am trying to think ahead when it might grow. I want to try "future-proof" my code ;)

Hi Author,
You can ignore * as always. I just gave you the example. One should always use column names instead of *.

--
Select col1,col2... 
From Yourtable
Order by Rand()
Limit 3;
--

Select allOpen in new window

Since you are just fetching 3 records this should not a be problem even if you have large number of rows. Since now you have only few rows this shall be really quick. As we need to have random records every time the query executed we have to use the RAND() function.

Hope it helps!

Also try

Select [CustomerID]
from (SELECT [CustomerID]
      ,[CompanyName]
      ,[ContactName]
      ,[ContactTitle]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[Phone]
      ,[Fax]
	  , ROW_NUMBER() OVER (ORDER BY NEWID()) rn
  FROM [Northwind].[dbo].[Customers]) D
  where rn<4

Select allOpen in new window

I have prepared this code using Northwind database. I will post sample outputs shortly.

1st try:
FRANS
RICSU
DRACD

2nd try:
BERGS
CHOPS
ROMEY

3rd try:
FURIB
BOLID
COMMI

Select allOpen in new window

@Mike - This question is not for MS SQL SERVER. This question is for MySQL.

Also the method you gave will not help in any case. It will add unnecessary burden on the DB Engine. Why I should get row numbers for all the rows present in the table and then put a condition < 4.

One can directly use below.

SELECT TOP 3 col1,col2 from yourtable
ORDER BY newId()

Hi Pawan,

Thank you for the comment. I enjoy your lightning solutions/posts. You are very sharp.

I don't have MySQL to test this. Doesn't the same work in MySQL?

Mike

MySQL does not support ROW NUMBER.