Select a random row from a table's top 10 rows ranked by sales_count

Question

I have a Products table on SQL Server. How can I select a random row from the top 10 rows - ranked by sales_count?

After finding an answer for how to select random rows, this is what I have so far:

SELECT * FROM (
    SELECT TOP 1 * FROM
        (
        SELECT TOP 10 *
        From Products
        ORDER BY "sales_count" DESC
        ) a
    ORDER BY NEWID()) b
    )

But I'm not sure it's correct?

Answer 1

There is one additional layer of subquery in your version. So assuming the columns and tables are correct, this should work:

SELECT TOP 1 p.*
FROM (SELECT TOP 10 p.*
      FROM Products p
      ORDER BY "sales_count" DESC
     ) p
ORDER BY NEWID();

If you wanted to be fair, you might add WITH TIES:

SELECT TOP (1) p.*
FROM (SELECT TOP (10) WITH TIES p.*
      FROM Products p
      ORDER BY "sales_count" DESC
     ) p
ORDER BY NEWID();