Re: Does MySQL know the SELECT TOP statement?

[Mehdi Pieloor]

The Limit Function, however, doesn't work inside subqueries... and if you want a top ten of things whose counter can have identical values (top scores, hit counts in a links database, stuff like that), you're out of luck.

Here's something I just came up with:

SELECT * FROM tblLinks a
INNER JOIN tblHitcount b ON a.linkID=b.linkID
-- Make sure only results are shown where NUMBER OF HITS equals
-- or is bigger than the LOWEST number in the TOP 25 hits
WHERE b.hitCnt >= (
SELECT MIN(hitCnt)
-- Lowest value in top 25
FROM(
SELECT DISTINCT hitCnt
FROM tblHitcount
ORDER BY hitCnt DESC
LIMIT 25
-- top 25 hits
)
AS tblTemp
)
ORDER BY b.hitCnt DESC