Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Post Unanswered: How to do it in one insert statement? Please come in

    Back from the vacation, I find I can't remember something. My problem is, for example, I have 2 tables. Table 1 has 3 columns: id, saledate and amount. Table 2 has just 2 columns: id and amount. Now I want to select the latest 'amount' for each 'id' from table 1 and insert into empty table 2. Is that possible to do it in one insert statement? What's the best way to do it anyways?

    Thanks a lot!

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    In your table1, is id the primary key?

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    No, id and saledate are primary key. Any thoughts? Thanks anyways.

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    In the Table1, do you have a primary key from one colomn, i.e., TableID?

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    insert into table2
    select id, max(amount)
    from table1 t1
    where saledate = (select max(saledate) from table1 where id = t1.id)
    group by id;

    The "max(amount)" and "group by id" return the maximum amount, should multiple amounts exist for the latest salesdate for a given id.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Sep 2003
    Posts
    3

    Thumbs up Thanks!

    Yes, it works! Thanks a lot!

Posting Permissions

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