By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,747 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,747 IT Pros & Developers. It's quick & easy.

SQL Question: Query for Newest Records

P: n/a
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_pkey" 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(timestamp) 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.
Nov 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.