SQL Ranks

use pubs
SET NOCOUNT ON
go

-- Approach 1.   Standard SQL approach using view.
-- Is expensive to run.
if (isnull(object_id('ranked_sales'),0)) > 0
        DROP VIEW ranked_sales
go

CREATE VIEW ranked_sales (rank, title_id, ytd_sales, title) 
AS
SELECT 
(SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2
    WHERE T2.ytd_sales >= T1.ytd_sales ) AS rank,
title_id,
ytd_sales,
title
FROM titles AS T1 WHERE ytd_sales IS NOT NULL
GO

SELECT * FROM ranked_sales 
WHERE rank <= 10
ORDER BY rank 
GO


-- Approach 2.   Without the view. nest a select in select list, and correlate back.   
-- Also expensive to run.
SELECT rank, title_id, ytd_sales, title
FROM (SELECT  
    T1.title_id, 
    ytd_sales, 
    T1.title, 
        (SELECT COUNT(DISTINCT T2.ytd_sales) FROM titles AS T2
        WHERE T1.ytd_sales <= T2.ytd_sales) AS rank
        FROM titles AS T1) AS X
    WHERE ytd_sales IS NOT NULL
    ORDER BY rank
GO

-- Approach 3.   Create a temp table with an identity, then do an ordered select to populate it.
CREATE TABLE #ranked_order
(
rank         int         IDENTITY NOT NULL PRIMARY KEY ,
title_id     char(6)     NOT NULL,
ytd_sales    int         NOT NULL,
title        varchar(80) NOT NULL
)
GO

INSERT #ranked_order 
    SELECT title_id, ytd_sales, title FROM titles WHERE ytd_sales
    IS NOT NULL ORDER BY ytd_sales DESC

SELECT * FROM #ranked_order

DROP TABLE #ranked_order
GO


-- Approach 4A.   Create a temp table with an identity, then do an
-- ordered select to populate it.
-- DO a nested select correlated back to itself to find the lowest
-- rank for a given value.
CREATE TABLE #ranked_order
(
rank        int         IDENTITY NOT NULL ,
title_id    char(6)     NOT NULL,
ytd_sales   int         NOT NULL,
title       varchar(80) NOT NULL
)
GO

INSERT #ranked_order
    SELECT title_id, ytd_sales, title FROM titles WHERE ytd_sales
        IS NOT NULL ORDER BY ytd_sales DESC


SELECT B.rank, A.title_id, B.ytd_sales , A.title
FROM 
(SELECT MIN(T2.rank) AS rank, T2.ytd_sales FROM #ranked_order AS T2
    GROUP BY T2.ytd_sales) AS B,
#ranked_order AS A
WHERE A.ytd_sales=B.ytd_sales
ORDER BY B.rank 

DROP TABLE #ranked_order
GO

-- Approach 4B.  Same as above, explicitly noting the ties.
CREATE TABLE #ranked_order
(
rank        int         IDENTITY NOT NULL ,
title_id    char(6)     NOT NULL,
ytd_sales   int         NOT NULL,
title       varchar(80) NOT NULL
)
GO

INSERT #ranked_order
    SELECT title_id, ytd_sales, title FROM titles WHERE ytd_sales
        IS NOT NULL ORDER BY ytd_sales DESC

SELECT B.rank,
CASE B.number_tied
    WHEN 1 THEN ' '
    ELSE '('+ CONVERT(varchar, number_tied) + ' Way Tie)'
    END AS tie,
A.title_id,
B.ytd_sales, 
A.title
FROM 
(SELECT MIN(T2.rank) AS rank, COUNT(*) AS number_tied, T2.ytd_sales
FROM #ranked_order AS T2 GROUP BY T2.ytd_sales) AS B,
#ranked_order AS A
WHERE A.ytd_sales=B.ytd_sales
ORDER BY B.rank

DROP TABLE #ranked_order
GO