RANK Challenge

[rgarrison]

[This thread is associated with Part 19 of the SQL Credit series.]

Using the sample table and data presented here, can you come up with a worthwhile SELECT statement that uses RANK() or DENSE_RANK() in both the SELECT and ORDER BY clauses where they don’t match?

If this is never possible, can you explain why?

[saharafrog]

If your sample data were entered differently, say in the order the competing produce were entered for judging, you could award an "early bird" prize to help encourage earlier entrants (and thereby get your weighing done ahead of time). Under this scenario, COLID would reflect the order the entries were received. The "early bird" prize could be given to the weightiest entrant among the first three entered in that category.

SELECT TOP 9
Entrant,
Weight,
Category,
DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC, COLID
) AS [Rank]
FROM #ContestResults
--WHERE Category = 'Watermelon'
ORDER BY DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY COLID
)
go

--SF

[dummy]

[This thread is associated with Part 19 of the SQL Credit series.]

Using the sample table and data presented here, can you come up with a worthwhile SELECT statement that uses RANK() or DENSE_RANK() in both the SELECT and ORDER BY clauses where they don’t match?

If this is never possible, can you explain why?

Query uses to list all information of the contesters whose names occur in
the contestresults table more than one for QA and cross check data.

WITH t
AS (
SELECT *,
dense_rank() over (
partition by entrant
order by weight DESC
) AS DenseRank

FROM dbo.ContestResults
)
select * from t
where entrant in
(SELECT distinct entrant
FROM t
where denseRank > 1)
order by entrant