Is there a way to present top 5 and bottom 5 and the order by a column?

Is there a way to present top 5 and bottom 5 and the order by a column?

I have 2 queries looking like this:

SELECT * 
FROM
    (SELECT DISTINCT TOP(5) 
         OG.GAMES AS 'Olympic Games', OC.CITY AS 'Hosting City', 
         OG.Expenses_Mil AS 'Financial Expenses (in multiples of 1M$)' 
     FROM 
         [Olympics].[Events_Facts] AS OEF 
     JOIN 
         [Olympics].[Games] AS OG ON OEF.GAMES_ID = OG.GAMES_ID
     JOIN 
         [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
     ORDER BY 
         OG.Expenses_Mil DESC) A

UNION ALL

SELECT * 
FROM 
    (SELECT DISTINCT TOP(5) 
         OG.GAMES AS 'Olympic Games', OC.CITY AS 'Hosting City', 
         OG.Expenses_Mil AS 'Financial Expenses (in multiples of 1M$)' 
     FROM 
         [Olympics].[Events_Facts] AS OEF 
     JOIN 
         [Olympics].[Games] AS OG ON OEF.GAMES_ID = OG.GAMES_ID
     JOIN 
         [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
     ORDER BY 
         OG.Expenses_Mil) B

I want that the final output will order the expense column in a DESC order, but I can't operate on the nested column.

The output is:

2016 Summer Rio de Janeiro    10
2014 Winter Sochi              8.2
2012 Summer London             8
2002 Winter Salt Lake City     7
2004 Summer Athina             7
1900 Summer Paris              1
1904 Summer St. Louis          1
1906 Summer Athina             1
1912 Summer Stockholm          1.3
1920 Summer Antwerpen          1.4

while the desired output should be in a DESC order according the last column

2 answers

  • answered 2021-11-28 20:29 John Cappelletti

    You can use the window function row_number() over(). If by chance there are ties, and you want to see them ... you can use dense_rank() instead

    Example

    with cte as (
    SELECT OG.GAMES AS [Olympic Games]
         , OC.CITY AS [Hosting City]
         , OG.Expenses_Mil AS [Financial Expenses (in multiplies of 1M$)]
         , RN1 = row_number() over (order by OG.Expenses_Mil asc)
         , RN2 = row_number() over (order by OG.Expenses_Mil desc)
    FROM [Olympics].[Events_Facts] AS OEF 
    JOIN [Olympics].[Games]  AS OG ON OEF.GAMES_ID = OG.GAMES_ID
    JOIN [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
    )
    Select [Olympic Games]
          ,[Hosting City]
          ,[Financial Expenses (in multiplies of 1M$)]
     From  cte 
     Where RN1<=5
        or RN2<=5
     Order by [Financial Expenses (in multiplies of 1M$)] desc
    

    Just a side note: The window functions can be invaluable. They are well worth your time getting comfortable with them.

  • answered 2021-11-28 21:04 Charlieface

    @JohnCappelletti's answer is very good. However it does require two sorts, once for ascending and once for descending.

    Here is a solution that uses a single sort:

    We use LEAD with an offset to check if 5 rows later is missing.

    with cte as (
      SELECT OG.GAMES AS [Olympic Games]
           , OC.CITY AS [Hosting City]
           , OG.Expenses_Mil AS [Financial Expenses (in multiplies of 1M$)]
           , RN = row_number() over (order by OG.Expenses_Mil desc)
           , ID5 = lead(OEF.ID, 5) over (order by OG.Expenses_Mil desc)
      FROM [Olympics].[Events_Facts] AS OEF 
      JOIN [Olympics].[Games]  AS OG ON OEF.GAMES_ID = OG.GAMES_ID
      JOIN [Olympics].[Cities] AS OC ON OEF.CITY_ID = OC.CITY_ID
    )
    Select [Olympic Games]
          ,[Hosting City]
          ,[Financial Expenses (in multiplies of 1M$)]
     From  cte 
     Where RN <= 5
        or ID5 IS NULL
     Order by [Financial Expenses (in multiplies of 1M$)] desc
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum