CODEWITHSUNDEEP
sqlsql-servert-sql
xpt
xpt

Reputation: 23054

get ROW NUMBER of random records

For a simple SQL like, 

SELECT top 3 MyId FROM MyTable ORDER BY NEWID()

how to add row numbers to them so that the row numbers become 1,2, and 3?

UPDATE:

I thought I can simplify my question as above, but it turns out to be more complicated. So here is a fuller version -- I need to give three random picks (from MyTable) for each person, with pick/row number of 1, 2, and 3, and there is no logical joining between person and picks. 

SELECT * FROM Person
LEFT JOIN (
  SELECT top 3 MyId FROM MyTable ORDER BY NEWID()
) D ON 1=1

The problem with above SQL are,

Here is a working SQL to test it out:

SELECT TOP 15 database_id, create_date, cs.name FROM sys.databases
CROSS apply ( 
  SELECT top 3 Row_number()OVER(ORDER BY (SELECT NULL)) AS RowNo,*
  FROM (SELECT top 3 name from sys.all_views ORDER BY NEWID()) T
  ) cs

So, Please help.

NOTE: This is NOT about MySQL byt T-SQL as their syntax are different, Thus the solution is different as well.

Upvotes: 0

Views: 7436

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Add Row_number to outer query. Try this 

SELECT Row_number()OVER(ORDER BY (SELECT NULL)),*
FROM   (SELECT TOP 3 MyId
        FROM   MyTable
        ORDER  BY Newid()) a

Logically TOP keyword is processed after Select. After Row Number is generated random 3 records will be pulled. So you should not generate Row Number in original query

Update

It can be achieved through CROSS APPLY. Replace the column names inside cross apply where clause with valid column name from Person table

SELECT *
FROM   Person p
       CROSS apply (SELECT Row_number()OVER(ORDER BY (SELECT NULL)) rn,*
                    FROM   (SELECT TOP 3 MyId
                            FROM   MyTable
                            WHERE  p.some_col = p.some_col -- Replace it with some column from person table
                            ORDER  BY Newid())a) cs

Upvotes: 5

Related Questions