SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by dates that match criteria

    I have the following query that outputs the users that received the highest number of favorites in the past week in descending order:
    Code MySQL:
    SELECT COUNT(faves.user_id) AS topFaves, faves.created_on, user_name
    FROM users
    INNER JOIN faves ON faves.user_id= users.id
    WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= created_on
    GROUP BY id ORDER BY topFaves DESC

    I would like to be able to extend this list to contain all users, not just from the past week, but still order them by the same criteria (the number of favorites they got in the past week and not by the number of faves they have in total).
    I tried to include a subquery in the select but didnt have any luck with it.

    Thanks in advance for any help

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      COUNT(faves.user_id) AS topFaves,
      faves.created_on,
      users.user_name
    FROM 
      users
    LEFT OUTER JOIN
      faves
    ON
      faves.user_id = users.id
    AND
      faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
    GROUP BY
      users.id
    ORDER BY 
      topFaves DESC

  3. #3
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great! thanks, Dan

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    dan, your query is fine, except for the created_on column in the SELECT clause, which will have an arbitrary value

    it will of course conform to the AND condition, but you won't know which of the faves it came from
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right now the query returns the "topFaves" alias which is the number of faves the user got this week. What if I also want to return the number of faves that user got ever? Will I need a different query for that or can I add it to this one?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT users.user_name
         , COUNT(CASE WHEN faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
                      THEN 'latest' ELSE NULL END) AS latestFaves
         , COUNT(faves.created_on) AS totalFaves
      FROM users
    LEFT OUTER 
      JOIN faves
        ON faves.user_id = users.id
    GROUP 
        BY users.id
    ORDER 
        BY topFaves DESC
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jul 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT users.user_name
         , COUNT(CASE WHEN faves.created_on >= CURRENT_DATE - INTERVAL 7 DAY
                      THEN 'latest' ELSE NULL END) AS latestFaves
         , COUNT(faves.created_on) AS totalFaves
      FROM users
    LEFT OUTER 
      JOIN faves
        ON faves.user_id = users.id
    GROUP 
        BY users.id
    ORDER 
        BY topFaves DESC
    Many thanks. works like a charm


Tags for this Thread

Bookmarks

Posting Permissions

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