If there are two highest value, how to print both-CodePudding

SELECT
    CONCAT(First_Name, ' ' , Last_Name) AS [Player Name], GWG 
FROM
    nhl 
ORDER BY
    GWG DESC;

Here there are two players that have the highest values of 12; I need to print both names without using TOP or limit. I'm using SQL Server.

CodePudding user response：

I suspect you're not aware of with ties.

Using

Select top(1) with ties
  CONCAT(First_Name, ' ' , Last_Name) as [Player Name], 
  GWG 
from nhl 
order by GWG desc;

Will give you only the row(s) with the maximum value of GWG

CodePudding user response：

Why the focus on NOT using TOP? Typically questions that seek to avoid some feature often are "solution" to different issues that are better addressed in a different manner. But here is one method.

with cte as (select max(GWG) as mGWG from dbo.nhl)
select ... 
  from dbo.nhl as nhl 
 inner join cte on nhl.GWG = cte.mGWG
order by ...
;