Re: query assistance -return most recent date

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/11/05

  • Next message: shif: "RE: err" 
    Date: Fri, 11 Feb 2005 21:11:39 +0100

    On Fri, 11 Feb 2005 08:35:07 -0800, Rich_A2B wrote:

    >That works, thanks! Now to complicate things, I have a third field,
    >DEL_RECIP_NAME. There can exist records where PKG_NUM is the same, but both
    >DEL_DATE_TIME and DEL_RECIP_NAME are different. How do I show all three
    >fields in the query result, but only show records with the most recent
    >DEL_DATE_TIME?

    Hi Rich_A2B,

    I guess I should have seen that one coming :-)

    SELECT a.pkg_num, a.del_date_time, a.del_recip_name
    FROM MyTable AS a
    WHERE NOT EXISTS (SELECT *
                      FROM MyTable AS b
                      WHERE b.pkg_num = a.pkg_num
                      AND b.del_date_time > a.del_date_tim)

    or

    SELECT a.pkg_num, a.del_date_time, a.del_recip_name
    FROM MyTable AS a
    INNER JOIN (SELECT pkg_num, MAX(del_date_time) AS max_del_date_time
                FROM MyTable
                GROUP BY pkg_num) AS b
          ON a.pkg_num = b.pkg_num
          AND a.del_date_time = b.max_del_date_time

    or

    SELECT a.pkg_num, a.del_date_time, a.del_recip_name
    FROM MyTable AS a
    WHERE a.del_date_time = (SELECT MAX(del_date_time)
                                   FROM MyTable AS b
                                   WHERE b.pkg_num = a.pkg_num)

    Best, Hugo 

    -- 
(Remove _NO_ and _SPAM_ to get my e-mail address)
  • Next message: shif: "RE: err"