Finding last N records by some_id

[Felix Geerinckx]

Here is a problem I've encountered a few times lately:

Given

CREATE TABLE foo(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
some_id INT NOT NULL,
ts TIMESTAMP NOT NULL
);

find the last (ordered by ts) N id's for each some_id, with N > 1.

Is this possible in "pure" (i.e. without application code) MySQL 4.1.x?

--
felix

[Mike A.]

I don't have time to test it, but something like:

SELECT id
FROM foo
ORDER BY ts DESC
LIMIT N;

should do it for you. My idea is order them most recent first then limit
the number of rows returned. Hope this helps.

-Mike

On Wed, 27 Apr 2005 11:06:52 +0000, Felix Geerinckx wrote:

Here is a problem I've encountered a few times lately:

Given

CREATE TABLE foo(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
some_id INT NOT NULL,
ts TIMESTAMP NOT NULL
);

find the last (ordered by ts) N id's for each some_id, with N > 1.

Is this possible in "pure" (i.e. without application code) MySQL 4.1.x?

--
Mike Argy
Custom Office solutions
and Windows/UNIX/OSX programs