Trying to limit results in subquery

Databases
1

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:


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

Because the LIMIT is applied to the rows that the subquery returns. Not to the rows used for the SUM.

Try this


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

Thanks a lot guido2004,

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


...
FROM links
WHERE links.post_id=20

// And not:
WHERE links.id=20

That way I tried the following:


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:


id 	title 	rating
7 	linkA 	3 #Should be 1
20 	linkB 	3

Table:


// 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

Couldn’t find the edit button but, the WHERE in my second query should be:


WHERE links.post_id = 111