Multiple grouping by

Question

Lets say I have a table with business listings.

I'd like my result set to show in this order, but with one query:

First: Show the 5 most recently created listings in order of created_date. Then: Show the rest of them but in random order.

My fields:

[BusinessName]
[Date_Created]

So if I had 100 businesses in the table, I want the list to show the 5 most recently created ones, and then show the rest but in random order.

Thank you in advance for your help!

Answer 1

Option A - Separating top 5 and the rest into two sub-queries and selecting them with UNION

WITH CTE_TOP5 AS 
(
    SELECT TOP 5  BusinessName, Date_Created, ROW_NUMBER() OVER (ORDER BY DATE_CREATED DESC) RN FROM dbo.YourTable
    ORDER BY Date_Created DESC 
)
, CTE_REST AS 
(
    SELECT  BusinessName, Date_Created FROM dbo.YourTable
    EXCEPT 
    SELECT  BusinessName, Date_Created  FROM CTE_TOP5
)
,CTE_RESTRANDOM AS
(
    SELECT  BusinessName, Date_Created, ROW_NUMBER() OVER (ORDER BY NEWID()) + 5 RN FROM CTE_REST
)
SELECT * FROM CTE_TOP5
UNION ALL
SELECT * FROM CTE_RESTRANDOM
ORDER BY RN

Option B - CASE in ORDER BY

;WITH CTE_TOP5 AS 
(
    SELECT TOP 5 *, ROW_NUMBER() OVER (ORDER BY DATE_CREATED DESC) RN FROM dbo.YourTable
    ORDER BY Date_Created DESC 
)
SELECT yt.*
FROM dbo.YourTable yt
LEFT JOIN CTE_TOP5 t5 ON yt.BusinessName = t5.BusinessName 
                     AND yt.Date_Created = t5.Date_Created
ORDER BY CASE WHEN t5.RN IS NOT NULL THEN t5.RN ELSE 6 END, NEWID()

Option C - Similar like B, but no CTE, ROW_NUMBERS and JOINS - whole logic goes in ORDER BY

SELECT *
FROM dbo.YourTable yt
ORDER BY CASE WHEN yt.Date_Created IN (SELECT TOP 5 yt2.Date_Created 
                                       FROM dbo.YourTable yt2 
                                       ORDER BY yt2.Date_Created DESC)
                 THEN yt.Date_Created 
              ELSE '1900-01-01' 
          END DESC, NEWID()