Search This Blog

Wednesday, June 25, 2008

Select a random row from a database table

This can be achieved by many means here a very simple way is given which shows a random row from the database but syntax for different database is different.

Select a random row with MySQL:

SELECT column FROM table
ORDER BY RAND()
LIMIT 1

Select a random row with PostgreSQL:

SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1

Select a random row with Microsoft SQL Server:

SELECT TOP 1 column FROM table
ORDER BY NEWID()

Select a random row with IBM DB2

SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY

Select a random record with Oracle:

SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1

1 comment:

  1. I didn't heard about this random word. This is new information for me. How you got this information? Really it is very nice. Thanks for sharing this information.

    ReplyDelete

Searching duplicate Record

Very often we have to look for duplicate records in the database, Below is the query which can give you the result. SELECT COL1, COL2 , ...