Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Cool Unanswered: Help with select for toplist

    Hello!! I have a toplist which picks out five top items per day from a list.
    Columns are ID (serial), ITEM (varchar), weekday (INT), and SCORE (INT)

    The select for top five competitors for day X :

    Code:
    is SELECT id, item, score FROM mytable WHERE weekday=X ORDER BY score DESC LIMIT 5
    Also I have this page where all results for a week is presented, and rather than doing seven selects (for X in (0..6)) how can I make a select for top five of each day)

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Which version of pg are you using? If its 8.4 or newer you can use analytic functions.

    Code:
    SELECT * FROM (
      SELECT id, item, weekday, score,
        RANK() OVER (PARTITION BY weekday ORDER BY score DESC) rnk
      FROM mytable
    ) sub
    WHERE rnk <= 5

  3. #3
    Join Date
    Dec 2010
    Posts
    2
    Very nice thank you! At first I didnt think it was working, but then i realized I hade some entries without score so when adding like this;

    Code:
    SELECT * FROM (
      SELECT id, item, weekday, score,
        RANK() OVER (PARTITION BY weekday ORDER BY score DESC) rnk
      FROM mytable WHERE score > 0
    ) sub
    WHERE rnk <= 5
    I did get the result I expected!!

Posting Permissions

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