How to randomly select rows in SQL

Question

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

User · Answer

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

User · Answer

If you are using large table and want to access of 10 percent of the data then run this following command  SELECT TOP 10 PERCENT   FROM Table1 ORDER BY NEWID

User · Answer

SELECT   FROM TABLENAME ORDER BY random   LIMIT 5

User · Answer

Maybe this site will be of assistance   For those who don t want to click through   SELECT TOP 1 column FROM table ORDER BY NEWID

User · Answer

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    DECLARE  minValue int  DECLARE  maxValue int  SELECT  minValue   min id    maxValue   max id  from  TABLE    DECLARE  randomId1 int   randomId2 int   randomId3 int   randomId4 int   randomId5 int SET  randomId1      maxValue   1  -  minValue    Rand      minValue SET  randomId2      maxValue   1  -  minValue    Rand      minValue SET  randomId3      maxValue   1  -  minValue    Rand      minValue SET  randomId4      maxValue   1  -  minValue    Rand      minValue SET  randomId5      maxValue   1  -  minValue    Rand      minValue  --select  maxValue as MaxValue   minValue as MinValue --     randomId1 as SelectedId1 --     randomId2 as SelectedId2 --     randomId3 as SelectedId3 --     randomId4 as SelectedId4 --     randomId5 as SelectedId5  select   from  TABLE  el where el id in   randomId1   randomId2   randomId3   randomId4   randomId5    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   DECLARE  NumItems int   100   DECLARE  minValue int  DECLARE  maxValue int  SELECT  minValue   min id    maxValue   max id  from  TABLE   DECLARE  range int    maxValue 1 -  minValue   with cte  n  as      select 1 union all    select n 1 from cte    where n  lt   NumItems   select cast   range   rand cast newid   as varbinary 100       minValue as int  tp into  Nt from cte   select   from  Nt ntt inner join  TABLE  i on i id   ntt tp   drop table  Nt

User · Answer

I have found this to work best for big data   SELECT TOP 1 Column Name FROM dbo Table TABLESAMPLE 1 PERCENT     TABLESAMPLE n ROWS  or TABLESAMPLE n PERCENT  is random but need to add the TOP n to get the correct sample size   Using NEWID   is very slow on large tables

User · Answer

SELECT TOP 5 Id  Name FROM customerNames ORDER BY NEWID     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 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   Select a random row with sqlite   SELECT column FROM table  ORDER BY RANDOM   LIMIT 1

User · Answer

SELECT TOP 5 Id  Name FROM customerNames ORDER BY NEWID

User · Answer

In case someone wants a PostgreSQL solution   select id  name from customer order by random   limit 5

User · Answer

In order to shuffle the SQL result set  you need to use a database-specific function call   Note that sorting a large result set using a RANDOM function might turn out to be very slow  so make sure you do that on small result sets  If you have to shuffle a large result set and limit it afterward  then it s better to use something like the Oracle SAMPLE N  or the TABLESAMPLE in SQL Server or PostgreSQL instead of a random function in the ORDER BY clause   So  assuming we have the following database table   And the following rows in the song table    id   artist                            title                                 ---- --------------------------------- ------------------------------------    1    Miyagi  amp           ft              I Got Love                             2    HAIM                              Don t Save Me  Cyril Hahn Remix        3    2Pac ft  DMX                      Rise Of A Champion  GalilHD Remix      4    Ed Sheeran  amp  Passenger            No Diggity  Kygo Remix                 5    JP Cooper ft  Mali-Koa            All This Love                         Oracle On Oracle  you need to use the DBMS RANDOM VALUE function  as illustrated by the following example  SELECT     artist    -    title AS song FROM song ORDER BY DBMS RANDOM VALUE  When running the aforementioned SQL query on Oracle  we are going to get the following result set    song                                                 ---------------------------------------------------    JP Cooper ft  Mali-Koa - All This Love                2Pac ft  DMX - Rise Of A Champion  GalilHD Remix      HAIM - Don t Save Me  Cyril Hahn Remix                Ed Sheeran  amp  Passenger - No Diggity  Kygo Remix       Miyagi  amp           ft            - I Got Love          Notice that the songs are being listed in random order  thanks to the DBMS RANDOM VALUE function call used by the ORDER BY clause   SQL Server On SQL Server  you need to use the NEWID function  as illustrated by the following example  SELECT     CONCAT CONCAT artist    -     title  AS song FROM song ORDER BY NEWID    When running the aforementioned SQL query on SQL Server  we are going to get the following result set    song                                                 ---------------------------------------------------    Miyagi  amp           ft            - I Got Love          JP Cooper ft  Mali-Koa - All This Love                HAIM - Don t Save Me  Cyril Hahn Remix                Ed Sheeran  amp  Passenger - No Diggity  Kygo Remix       2Pac ft  DMX - Rise Of A Champion  GalilHD Remix      Notice that the songs are being listed in random order  thanks to the NEWID function call used by the ORDER BY clause   PostgreSQL On PostgreSQL  you need to use the random function  as illustrated by the following example  SELECT     artist    -    title AS song FROM song ORDER BY random    When running the aforementioned SQL query on PostgreSQL  we are going to get the following result set    song                                                 ---------------------------------------------------    2Pac ft  DMX - Rise Of A Champion  GalilHD Remix      JP Cooper ft  Mali-Koa - All This Love                Ed Sheeran  amp  Passenger - No Diggity  Kygo Remix       HAIM - Don t Save Me  Cyril Hahn Remix                Miyagi  amp           ft            - I Got Love          Notice that the songs are being listed in random order  thanks to the random function call used by the ORDER BY clause   MySQL On MySQL  you need to use the RAND function  as illustrated by the following example  SELECT   CONCAT CONCAT artist    -     title  AS song FROM song ORDER BY RAND    When running the aforementioned SQL query on MySQL  we are going to get the following result set    song                                                 ---------------------------------------------------    HAIM - Don t Save Me  Cyril Hahn Remix                Ed Sheeran  amp  Passenger - No Diggity  Kygo Remix       Miyagi  amp           ft            - I Got Love          2Pac ft  DMX - Rise Of A Champion  GalilHD Remix      JP Cooper ft  Mali-Koa - All This Love                Notice that the songs are being listed in random order  thanks to the RAND function call used by the ORDER BY clause

User · Answer

If you have a table with millions of rows and care about the performance  this could be a better answer   SELECT   FROM Table1 WHERE  ABS CAST     BINARY CHECKSUM    keycol1  NEWID     as int       100   lt  10   https   msdn microsoft com en-us library cc441928 aspx

[sql] How to randomly select rows in SQL?

Examples related to sql

Examples related to database

Examples related to random