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
