Results 1 to 3 of 3

Thread: RANK Challenge

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Question RANK Challenge

    [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?

  2. #2
    Join Date
    Nov 2008
    Location
    Iowa
    Posts
    1

    Ifs and buts

    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

  3. #3
    Join Date
    Nov 2008
    Posts
    1
    Quote Originally Posted by 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?
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •