#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Question SELECT DISTINCT on multiple fields


    Hi,

    I think I'm having difficulty trying to extract records using SELECT DISTINCT. I'm doing SELECT DISTINCT FLD1, FLD2, FLD3 and I get the following,

    FLD1 FLD2 FLD3
    ---------------------------
    Dept1 ABC 04-FEB-03
    Dept1 ABC 15-JUN-03
    Dept1 ABC 27-AUG-03

    Whic is not the result I want. I only need to get the last one with the lastest date which is

    Dept1 ABC 27-AUG-03

    How can I get the distinct record with the lastest date field ( FLD3 ) when there are multiple records on one field (FLD1) but different date value on other field ( FLD3 )?

    TIA for your help,
    Scottcka
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    1
    Rep Power
    0

    Post


    Hi Scottika ,

    u can get ur result this way ....

    select distinct T.FLD1,T.FLD2, T.FLD3
    from TABLE1 T
    where T.FLD3 = (select max(FLD3)
    from TABLE1
    where FLD1 = T.FLD1)

    try it out.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    sql Code:
    SELECT 
    fld1, fld2, MAX(fld3)
      FROM table1
    GROUP BY fld1, fld2
    Last edited by pabloj; August 1st, 2006 at 05:59 AM. Reason: Please no php code to highlight SQL ;)
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    7
    Rep Power
    0
    SELECT DISTINCT Field1, Field2 MAX(Field3)
    FROM Table
    GROUP BY Field1
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    2
    Rep Power
    0

    Thumbs up Re:problem


    Hi,

    if the data is like this,

    ID NAME DAT
    --- ------------------------------ ---------
    1 aa 12-JAN-06
    1 aa 13-JAN-05
    2 mm 14-JAN-05
    2 mm 04-MAR-06

    then use this query to get ur result
    " select id,name,dat from test1 where dat in (select max(dat) from test1 group by id,name);"

    The result will be :
    1 aa 12-jan-06
    2 mm 04-mar-06

    or if u have data like this

    ID NAME DAT
    --- ------------------------------ ---------
    1 aa 12-JAN-06
    1 aa 13-JAN-05
    1 aa 14-JAN-05
    1 aa 04-MAR-06

    " select id,name,dat from test1 where dat = (select max(dat) from test1 group by id,name);"

    the result will be:

    1 aa 04-mar-06

IMN logo majestic logo threadwatch logo seochat tools logo