how to select random recods or rows from sql server table including row no

My sql query is below

SELECT top 24 ROW_NUMBER() OVER (ORDER BY QuestionID) AS QuestionNo, Q.QuestionID From Questions Q
ORDER BY NEWID()

The first part " top 24 ROW_NUMBER() OVER (ORDER BY QuestionID) AS QuestionNo" gives me a row no. This part "ORDER BY NEWID()" gives me random rows. The results are like below

QuestionNo      QuestionID
2      aa
3      ss
1      qq


The above query first adds row no column to the other columns and then pulls the random records whereas I want the random questions first and then added row number to them e.g.

QuestionNo      QuestionID
1      aa
2      ss
3      qq

mmalik15Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
The ROW_NUMBER() function should cause the result set to be ordered by QuestionNo, but to make this more explicit to your intention, you can you do like this also:

SELECT QuestionNo, QuestionID
FROM (
   SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) AS QuestionNo
        , Q.QuestionID
   FROM Questions Q
) derived
WHERE QuestionNo <= 24
;

Hope that helps!
0
 
venk_rCommented:
You dont need to use order by NEWID() function.You willl get the desired result without using it as it will order by using the function by itself.
0
 
Kevin CrossChief Technology OfficerCommented:
You probably meant something more like this:

SELECT top 24 ROW_NUMBER() OVER (ORDER BY NEWID()) AS QuestionNo, Q.QuestionID
FROM Questions Q
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
venk_rCommented:
Thats correct. I should have sent the corrected sql .
0
 
HainKurtSr. System AnalystCommented:
what about

select top 24 * from (
select *, newid() myid from mytable
) order by myid
0
 
Kevin CrossChief Technology OfficerCommented:
HainKurt, myid will be a UNIQUEIDENTIFIER in that case and not a sequential 1-24.
0
 
HainKurtSr. System AnalystCommented:
got it :) what about this?

select top 24 *, row_number() over(order by myid) rowNo  from (
select *, newid() myid from mytable
) order by myid
0
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
oops, i guess there is no need for order by in this case

select top 24 *, row_number() over(order by myid) rowNo  from (
select *, newid() myid from mytable
) x
0
 
HainKurtSr. System AnalystCommented:
the query above adds newid to all rows, then order by this and add row numbers, then we select top 24

if it is same/similar to yours above, just ignore it :)
0
 
mmalik15Author Commented:
Excellent guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.