Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004

    Unanswered: SQL Question: Query for Newest Records

    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 "id" of the newest row 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.



  2. #2
    Join Date
    Apr 2003

    Thumbs up Trigger

    I'll push you to to write a trigger that saves the last record in a table, of the kind :
    Create table History(id int8,
    rank serial)

    Create Or Replace function ImNew Returns Opaque
    language 'sql'
    Select max (rank) from history

    Now create the trigger and that's all !
    Open up
    Take a look to my Blog

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    second table and trigger not necessary

    use a simple correlated subquery

    PHP Code:
    select id
      from records ZZ
     where timestamp 

    select max(timestamp)
    from records
              where number 
    ZZ.number | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts