April 28, 2012
applying multiple limits
Question by Jacob K
I have a table that goes as follows:
content_id | contact_count
1 23
2 4
3 89
I want to select the content_id with the highest contact_count from the last 25 rows of the table.
I’ve tried many different things such as:
select content_id from research_products_content
where contact_count=(select max(contact_count)
from research_products_content order by rating_total_id desc limit 25)
order by rating_total_id desc limit 1
Answer by Shedal
In your example, limit 25 is applied after the result (max, which is a single row) is selected. Try this instead:
SELECT tmp.content_id FROM (
SELECT *
FROM research_products_content
ORDER BY rating_total_id DESC
LIMIT 25
) AS tmp
WHERE tmp.contact_count = (
SELECT max(tmp2.contact_count)
FROM (
SELECT *
FROM research_products_content
ORDER BY rating_total_id DESC
LIMIT 25
) AS tmp2
)
LIMIT 1
Answer by Starx
Since the column number will be the same use UNION
select content_id from research_products_content
where contact_count=(select max(contact_count)
from research_products_content order by rating_total_id desc limit 25)
UNION
select content_id from research_products_content
where contact_count=(select max(contact_count)
from research_products_content order by rating_total_id desc limit 1
You might want to implement caching along the way
