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

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

CodePudding user response：

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.

CodePudding user response：

@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