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

    Question Unanswered: SQL Select last record per group

    I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.

    TABLE1 info
    ID1 fname1 lname1 DOB1
    ID2 fname2 lname2 DOB2
    ID3 fname3 lname3 DOB3

    TABLE2 info
    ID1 Visit2
    ID1 Visit3
    ID1 Visit1
    ID1 Visit4
    ID2 Visit1
    ID2 Visit2
    ID3 Visit1

    I need a view or SP to return the following:

    ID1 fname1 lname1 dob1 visit4
    ID2 fname2 lname2 dob2 visit2
    ID3 fname3 lname3 dob3 visit1

    It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!

  2. #2
    Join Date
    Sep 2003
    The extremely Royal borough of Kensington, London
    select t1.*, v.maxVisit
    from table1 t1
    (select, max(t2.visit) as maxVisit
    from table2 t2
    group by v ON =;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    EXCELLENT!, -- Thanks for the assistance
    Last edited by pebkac; 01-24-04 at 21:40.

Posting Permissions

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