Post reply

aggregate function in select

  • August 1, 2007 at 2:00 pm

    #125257

    I have two tables like this

    Table1:

    ID LName FName

    --------------

    1 LN1 FN1

    2 LN2 FN2

    3 NL3 FN3

    4 NL4 FN4

    Table2:

    ID LogDate LogType

    ---------------------------

    1 2006-08-01 A

    1 2006-07-01 B

    2 2006-07-30 A

    2 2006-07-15 C

    3 2006-08-01 A

    I want to select the ID, max(LogDate) for a person whole LName starts with LN. Any simple query to do this?

    Thanks

  • August 1, 2007 at 2:22 pm

    #723996

    select Table1.ID, Max(LogDate)

    From Table1 join Table2 on Table1.ID = Table2.ID

    Where LName like 'LN%'

    Group by Table1.ID

    Regards,
    Rubes

  • August 1, 2007 at 3:30 pm

    #724010

    Hi Rubes,

    I also need to select LogType which is not part of group by clause.

    Thanks

  • August 1, 2007 at 3:59 pm

    #724013

    Ok, what do you expect the results to look like?  What do you expect for LogType when there is different logtypes for a particular ID?

    Regards,
    Rubes

  • August 1, 2007 at 4:35 pm

    #724020

    Whatever the value is for that row that has the max(discharge_dttm).

    Thanks again

  • August 1, 2007 at 6:10 pm

    #724029

    Join result of rubes's query to Table2 on (ID + LogDate) and get desired result.

    _____________
    Code for TallyGenerator

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply