mardi 28 juillet 2015

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://ift.tt/1LQN73E




Aucun commentaire:

Enregistrer un commentaire