query assistance -return most recent date

I have a table that has two fields, pkg_num, which is a number, and
del_date_time, which is a date-time. The table can contain duplicate pkg_num
values, as long as the del_date_time values are different for any given
number. I need a query that will return the most recent del_date_time for
each pkg_num. Any ideas?
On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:

>I have a table that has two fields, pkg_num, which is a number, and
>del_date_time, which is a date-time. The table can contain duplicate pkg_num
>values, as long as the del_date_time values are different for any given
>number. I need a query that will return the most recent del_date_time for
>each pkg_num. Any ideas?
Hi Rich_A2B,
Probably
SELECT pkg_num, MAX(del_date_time)
FROM MyTable
GROUP BY pkg_num
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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?
"Hugo Kornelis" wrote:

> On Thu, 10 Feb 2005 09:17:01 -0800, Rich_A2B wrote:
>
> Hi Rich_A2B,
> Probably
> SELECT pkg_num, MAX(del_date_time)
> FROM MyTable
> GROUP BY pkg_num
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||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)
|||*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||

Quote:

Originally posted by Hugo Kornelis
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)