Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

0
mmalik15
Asked:
mmalik15
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
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
 
Kevin CrossChief 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
HainKurtSr. 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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now