?
Solved

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

Posted on 2011-09-04
10
Medium Priority
?
257 Views
Last Modified: 2012-05-12
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
Comment
Question by:mmalik15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:venk_r
ID: 36481614
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36481685
You probably meant something more like this:

SELECT top 24 ROW_NUMBER() OVER (ORDER BY NEWID()) AS QuestionNo, Q.QuestionID
FROM Questions Q
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 36481688
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 8

Expert Comment

by:venk_r
ID: 36481689
Thats correct. I should have sent the corrected sql .
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 36481718
what about

select top 24 * from (
select *, newid() myid from mytable
) order by myid
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36481728
HainKurt, myid will be a UNIQUEIDENTIFIER in that case and not a sequential 1-24.
0
 
LVL 57

Expert Comment

by:HainKurt
ID: 36481742
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
 
LVL 57

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 36481747
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
 
LVL 57

Expert Comment

by:HainKurt
ID: 36481758
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
 

Author Closing Comment

by:mmalik15
ID: 36485598
Excellent guys!
0

Featured Post

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question