从表中选择最上面的N行，然后按列排序

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

我需要从一个表中得到3个随机的行，然后按照BannerWeight列的顺序排列这些行。

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

我只是不知道如何排列这3个随机的行。我试着做

 ORDER BY BannerWeight, NEWID()

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

但这只会得到随机的3行其中BannerWeight是5。

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

这里有一个SQLFiddle: http://sqlfiddle.com/#!6/a8088/2/0

1 个解决方案

#1

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