On 17/11/2005, Peter Brodersen wrote:
> I would like to select top three from different parts in the same
> table, e.g. for the following data set:
USE test;
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
fid INT NOT NULL,
d INT NOT NULL
);
INSERT INTO foo VALUES
(1, 10), (1, 20), (1, 30), (1, 40),
(2, 10), (2, 20), (2, 30),
(3, 10),
(4, 10), (4, 20), (4, 20), (4, 20), (4, 30),
(5, 10), (5, 20), (5, 50), (5, 50), (5, 50), (5, 50);
SELECT
f1.fid, f1.d
,COUNT(f1.fid) AS Ties
FROM foo f1
WHERE
(SELECT COUNT(*)
FROM foo f2
WHERE f2.fid = f1.fid AND f2.d > f1.d
) < 3 -- top 3
GROUP BY f1.fid, f1.d
ORDER BY f1.fid, f1.d DESC;
which also tries to handle ties.
If you remove the 'COUNT() AS Ties' and the GROUP BY, you can have more
than three results per fid when there are ties.
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
