CODEWITHSUNDEEP
sql
Dave
Dave

Reputation: 5029

Select random x of top y records

I need to select 5 records randomly out of the top 100 records sorted by amount.

This can be broken into 2 queries but I do not know how to combine them without creating a function (which I'm thinking might be less efficient.

Query 1: SELECT TOP 100 from Cars order by price desc

Query 2: SELECT TOP 5 * FROM (Query1) ORDER BY NEWID()

I'm just not sure how to combine these, whether using an inner joing or just an inner select?

My first reaction was to try this which does not work:

SELECT TOP 5 * FROM (SELECT TOP 100 * FROM Cars order by Price desc) ORDER BY NEWID()

Upvotes: 0

Views: 101

Answers (1)

zimdanen
zimdanen

Reputation: 5626

You need to name your subquery:

SELECT TOP 5 * FROM (SELECT TOP 100 * FROM Cars order by Price desc) [A] ORDER BY NEWID()

Upvotes: 2

Related Questions