CODEWITHSUNDEEP
sqlsql-serverselectrandom
David
David

Reputation: 1570

Select Top N Random rows from table then ordering by column

I need to get 3 random rows from a table and then order those rows by a the BannerWeight column.

So if the data is:

BannerID     BannerWeight
   1               5
   2               5
   3               10
   4               5
   5               10

I want the results to be:

BannerID     BannerWeight
   5               10
   2               5
   4               5

So far I have:

SELECT TOP 3 b.BannerID, b.BannerWeight FROM CMS_Banner b
INNER JOIN CMS_BannerCategory c ON b.BannerCategoryID = c.BannerCategoryID
WHERE c.BannerCategoryName LIKE 'HomepageSponsors'
ORDER BY NEWID()

I just can't figure out how to order those 3 random rows once I get them. I've tried doing 

 ORDER BY BannerWeight, NEWID()

But this just gets me 3 random rows where the BannerWeight is 5.

Here is an SQLFiddle: http://sqlfiddle.com/#!6/a8088/2/0

Upvotes: 2

Views: 281

Answers (1)

APH
APH

Reputation: 4154

Easiest option (I think) is to use a subquery:

Select * from 
    (
    SELECT TOP 3 b.BannerID, b.BannerWeight FROM Banners b
    ORDER BY NEWID()
    ) a
order by a.bannerweight

Upvotes: 5

Related Questions