SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    May 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to limit results in subquery

    Hey everyone,
    I need to retrieve a sum of last 3 votes of a table. I tried limiting the results in my subquery but that doesn't seem to limit:

    Code SQL:
    SELECT links.id, links.title,
    	(
    	 SELECT SUM(ratings.VALUE)
     	 FROM ratings
    	 WHERE ratings.link_id=links.id
    	 ORDER BY ratings.id DESC
    	 LIMIT 3
    	) AS rating
    FROM links
    WHERE links.id=20
     
    // TABLE Ratings:
    id 	link_id  VALUE
    1 	20 	 1
    2 	20 	 1
    4 	20 	 1
    8 	20 	 1
    14 	20 	 1
     
    // RESULT:
    id 	title 	rating
    20 	linkA 	5
     
    // Expected:
    id 	title 	rating
    20 	linkA 	3

    Could someone give me a hand with this?
    I tried other ways but this was the closest I could get.

    Thanks!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Because the LIMIT is applied to the rows that the subquery returns. Not to the rows used for the SUM.

    Try this
    Code MySQL:
    SELECT 
        links.id
      , links.title
      , SUM(ratings.value) AS rating
    FROM links
    INNER JOIN 
      (SELECT
           link_id,
         , value
       FROM ratings
       WHERE link_id = 20
       ORDER BY id DESC
       LIMIT 3
      ) AS ratings
    ON links.id = ratings.link_id
    WHERE links.id = 20
    GROUP BY
        links.id
      , links.title

  3. #3
    SitePoint Member
    Join Date
    May 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot guido2004,

    That surely works, however I forgot a detail in my query on the first post:

    Code MySQL:
    ...
    FROM links
    WHERE links.post_id=20
     
    // And not:
    WHERE links.id=20

    That way I tried the following:
    Code MySQL:
    SELECT
           links.id,
           links.title,
           SUM(value) AS rating
    FROM links
    INNER JOIN
        (SELECT
                ratings.link_id,
                ratings.value,
                links.id
    	    FROM ratings
    	    LEFT JOIN links
                     ON ratings.link_id = links.id
    		 ORDER BY ratings.id DESC
    		 LIMIT 3
         ) AS Ratings
    WHERE links.post_id IN (20,7)
    GROUP BY links.id, links.title

    That returns:
    Code:
    id 	title 	rating
    7 	linkA 	3 #Should be 1
    20 	linkB 	3
    Table:
    Code:
    // TABLE Ratings:
    id  link_id  value
    1   20     1
    2   20     1
    3   7       1
    4   20     1
    5   7       0
    8   20     1
    14  20     1
    Can you help me with that?
    Thanks again

  4. #4
    SitePoint Member
    Join Date
    May 2006
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Couldn't find the edit button but, the WHERE in my second query should be:

    Code:
    WHERE links.post_id = 111


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
  •