Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Tampa, Florida
    Posts
    13

    Unanswered: Selecting last row

    I have 2 tables with 1 to many relationship.
    1st Table has ID, 2nd one has ID and NOTES columns, where ID is the KEY. I want to do a select so that i can only get the last NOTE for each ID from 1st Table. How do i do this one.

    Thanks

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    Dunno if you can "determine" wich id was last inserted.
    I presume u either use serail or sequence.

    If i wanted the last insert of a table, i'd make a timestamp colum: my_date

    Then select max(my_date) or sumthing.

    -Ed

  3. #3
    Join Date
    Aug 2003
    Posts
    32
    I haven't tested it, but I think you're looking for something like this:
    Code:
    create table one (id integer);
    create table two (id integer, notes varchar(50), one_id integer);
    insert into one (id) values (1);
    insert into one (id) values (2);
    insert into one (id) values (3);
    insert into two (id, notes) values (1, 'one first note', 1);
    insert into two (id, notes) values (2, 'one second note', 1);
    insert into two (id, notes) values (3, 'one third note', 1);
    insert into two (id, notes) values (4, 'two first note', 2);
    insert into two (id, notes) values (5, 'two second note', 2);
    insert into two (id, notes) values (6, 'two third note', 2);
    insert into two (id, notes) values (7, 'three first note', 3);
    insert into two (id, notes) values (8, 'three second note', 3);
    insert into two (id, notes) values (9, 'three third note', 3);
    select
      one.id,
      two.note as last_note
    from
      one left join
      two on two.id = (
        select 
          max(id)
        from
          two
        where
          two.one_id = one.id
      )

Posting Permissions

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