Get ROW NUMBER from random entries

Question

For simple SQL, for example

SELECT top 3 MyId FROM MyTable ORDER BY NEWID() 

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

UPDATE:

I thought I could simplify my question as stated above, but that turned out to be more complicated. So, here is a more complete version - I need to give three random choices (from MyTable ) for each person, with the choice / row number 1, 2 and 3, and there is no logical connection between the person and the choices.

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

The problem with the above SQL:

• Obviously you should add the selection / line number 1, 2, and 3.
• and what is not obvious is that the aforementioned SQL will provide each person the same selections, while I need to give different people different options

Here is the working SQL for testing:

 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 is different, so the solution is different.

Answer 1

Add Row_number to the external request. try it

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

Logically, the TOP keyword is processed after Select . After the line number is generated, a random 3 entries will be pulled out. Therefore, you should not generate a line number in the original query

Refresh

This can be achieved with CROSS APPLY . Replace column names inside cross apply where with a 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