Select random rows from table

Question

I want to select 24 Random rows from whole table of Admin_Pic_Lib, but this Query give syntax error,

 SELECT top 24 CAST( [Pictures] AS varbinary(Max)) FROM Admin_Pic_Lib ORDER BY NEWID()

 UNION ALL 

  SELECT  CAST([Pictures] AS varbinary(Max))   FROM User_images  WHERE UserName ='ahmed'

When I am trimming ORDER BY NEWID() it only select top 24 rows.

AFAIK `TOP` is applied _after_ `ORDER BY` happens. So the top half of your `UNION` query should already be returning 24 random rows. Can you elaborate on what you are seeing and why you think there is a problem? — Tim Biegeleisen, Jul 15 '16 at 07:56

score 0 · Accepted Answer · answered Jul 15 '16 at 09:24

Please try the below code.

SELECT Pictures 
FROM 
    (SELECT TOP 24 CAST([Pictures] AS varbinary(Max)) AS Pictures 
     FROM Admin_Pic_Lib ORDER BY NEWID()) AS X

 UNION ALL 

  SELECT  CAST([Pictures] AS varbinary(Max)) AS Pictures 
  FROM User_images
  WHERE UserName ='ahmed'

Select random rows from table

1 Answers1