'Alphabetic ranking a list with set of 'WITH TIES - ORDER BY (multiple fields)

A regular usage of "WITH TIES" & the result of the query :

Demand: The best of 10 movies as Oscar Wins

SELECT  TOP 10 WITH TIES  F.FilmName AS TITLE,
                      F.FilmOscarWins AS [OSCAR WINS] 
                      FROM DBO.tblFilm F
                      WHERE FilmOscarWins IS NOT NULL
                      ORDER BY  [OSCAR WINS] DESC;

Resullt of the first query

When I add "TITLE" at the field of ORDER BY to make the list alphabetical order, but it doesn't work properly!

  • No 13 movies in the list

  • No alphabetic order

Demand: The best of 10 movies in alphabetical order:

SELECT  TOP 10 WITH TIES  F.FilmName AS TITLE,
                      F.FilmOscarWins AS [OSCAR WINS] 
                      FROM DBO.tblFilm F
                      WHERE FilmOscarWins IS NOT NULL
                      ORDER BY  [OSCAR WINS] DESC, TITLE;

Result of the second query

So what should I do to make an alphabetical order while ranking it with set of 'WITH TIES - ORDER BY'?

sqlsql-serversql-order-by


Solution 1:[1]

Your query gets the top 10 ranked Oscar winning movies. This is what TOP 10 and ORDER BY are for in that query.

Now you want to use this data set and show it in another order. This means an additional query with its own ORDER BY clause:

SELECT *
FROM
(
  SELECT TOP 10 WITH TIES
    F.FilmName AS TITLE,
    F.FilmOscarWins AS [OSCAR WINS] 
  FROM DBO.tblFilm F
  WHERE FilmOscarWins IS NOT NULL
  ORDER BY [OSCAR WINS] DESC
) top10
ORDER BY TITLE;

Solution 2:[2]

Two ORDER BYs solve the problem. If you want to include the ranking, use rank():

SELECT f.*
FROM (SELECT  F.FilmName AS TITLE,F.FilmOscarWins AS [OSCAR WINS],
              RANK() OVER (ORDER BY [OSCAR WINS] DESC) as ranking
      FROM DBO.tblFilm F
      WHERE FilmOscarWins IS NOT NULL
     ) f
WHERE ranking <= 10
ORDER BY  [OSCAR WINS] DESC, Title;

Solution 3:[3]

You have to:

  • order the database by Oscar wins and select the top 10 movies (with ties)
  • select those movies and order them by Oscar wins and then alphabetically

Honestly there might be a simpler way to do this, but the other responses do not appear to generate the desired output, whereas this solution does.

SELECT 
    *
FROM
    (
        SELECT TOP 10 WITH TIES
            FilmName AS 'Title'
            ,FilmReleaseDate AS 'Released on'
            ,FilmRunTimeMinutes AS 'Duration'
            ,[FilmOscarWins]
        FROM
            [dbo].[tblFilm]
        WHERE 
            [FilmOscarWins] IS NOT NULL
        Order By
            [FilmOscarWins] DESC
    )
    top10WithTies
Order by
    [FilmOscarWins] DESC,
    'Title' ASC

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Thorsten Kettner
Solution 2 Gordon Linoff
Solution 3

Related Questions