In this post we will learn how to list random record from a table in sql server. We will list the records by a condition. For list random records we use newid function after order by clause. Then with top clause, we can list the records count we want. We will write queries from the library database added at below. You can write more examples dowloading the sample library database with data from the link.
Example 1: List random one student.
|
1 |
Select top 1 * from students order by newid() |
Example 2: List random one student from 10A class.
|
1 |
Select top 1 * from students where class='10A' order by newid() |
Example 3: List random three female students from 10A class.
|
1 |
Select top 3 * from students where class='10A' and gender='F' order by newid() |
Example 4: List random book from 5 books which has most page count.
|
1 2 3 |
Select top 1 * from (Select top 5 * from books order by pagecount desc) as T order by newid() |
Example 5: List random one book from most read books
|
1 2 3 4 5 6 7 |
Select top 1 * from (Select top 5 name,count(*) as Number from books,borrows where books.bookId = borrows.bookId group by name,books.bookId order by count(*) desc) as T order by newid() |
