mmalik15
asked on
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
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
venk_r
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.
You probably meant something more like this:
SELECT top 24 ROW_NUMBER() OVER (ORDER BY NEWID()) AS QuestionNo, Q.QuestionID
FROM Questions Q
SELECT top 24 ROW_NUMBER() OVER (ORDER BY NEWID()) AS QuestionNo, Q.QuestionID
FROM Questions Q
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thats correct. I should have sent the corrected sql .
what about
select top 24 * from (
select *, newid() myid from mytable
) order by myid
select top 24 * from (
select *, newid() myid from mytable
) order by myid
HainKurt, myid will be a UNIQUEIDENTIFIER in that case and not a sequential 1-24.
got it :) what about this?
select top 24 *, row_number() over(order by myid) rowNo from (
select *, newid() myid from mytable
) order by myid
select top 24 *, row_number() over(order by myid) rowNo from (
select *, newid() myid from mytable
) order by myid
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :)
if it is same/similar to yours above, just ignore it :)
ASKER
Excellent guys!