SQL Question: Query for Newest Records

[Ryan J. Allen]

Forgive me if this is the wrong group to post to.

I have a table and the relevant bits look like this:

CREATE TABLE "records" (
"id" SERIAL,
"number" integer NOT NULL,
"timestamp" timestamp with time zone NOT NULL,
CONSTRAINT "records_pk ey" PRIMARY KEY ("id")
);

Let's say there's 500 rows, and in the 500 rows the "number" column will
have any one of the values 1 to 10. Every value 1 to 10 is covered by
at least one row. The "timestamp" field has any timestamp value, up to
now. How can I get the newest "id" for each value in number?

If I do:
SELECT num,MAX(timesta mp) FROM records GROUP BY num;
I get results like:
num | max
---------------
0 | 2004-04-01 03:02:01-05
1 | 2004-03-29 13:22:04-05
2 | 2001-12-11 01:05:12-05
....
8 | 1999-11-07 12:32:22-05
9 | 2001-02-11 14:52:33-05
10 | 2002-06-22 14:34:44-05

I need the "id" that corresponds to those maximum rows.

I realize this can be achieved through a couple of queries and some
programming magic, but I get the feeling that this is something that SQL
can do for me in one query.

Any insight would be appreciated.

Thanks,


Ryan.