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

    Join Date
    Jan 2004
    Posts
    1
    Rep Power
    0

    Querly latest distinct values


    Hi,

    Sorry for this, I'm a newbie and I need help urgently:

    I have three values from two tables to query: TABLE1.DATE, TABLE1.VALUE, TABLE2.STATUS

    How do I query only the distinct values from TABLE1.VALUE and only the latest (based on the value of TABLE1.DATE)???

    (TABLE2 and TABLE1 are related using a column called SITE_ID, where TABLE2.SITE_ID = TABLE1.SITE_ID)



    Example:

    TABLE1.DATE-----TABLE1.VALUE------TABLE2.STATUS
    11/20/2003------------60--------------------1------
    11/19/2003------------40--------------------0------
    11/19/2003------------50--------------------1------
    11/18/2003------------40--------------------0------
    11/18/2003------------50--------------------1------
    11/17/2003------------20--------------------0------

    The query should return the following:

    TABLE1.DATE-----TABLE1.VALUE------TABLE2.STATUS
    11/20/2003------------60--------------------1------
    11/19/2003------------50--------------------1------
    11/19/2003------------40--------------------0------
    11/17/2003------------20--------------------0------


    Appreciate any help out there. Many, many thanks in advance!!!
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    Your query look like that:

    SELECT t1.date,t1.value, t2.status
    FROM table1 t1, table2 t2
    Where (date,value) in (SELECT max(date),value from table1 group by value)
    and t1.date = t2.date
    and t1.value = t2.value
    /

IMN logo majestic logo threadwatch logo seochat tools logo