Re: query assistance -return most recent date


Hugo Kornelis wrote:
> *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) *



--
resprog2
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1415285.html

.

Relevant Pages

  • Re: query assistance -return most recent date
    ... Now to complicate things, I have a third field, ... fields in the query result, but only show records with the most recent ... "Hugo Kornelis" wrote: ...
    (microsoft.public.sqlserver.mseq)
  • Re: Importing from another database on same server using SQL Server Express
    ... I thought this post was considered dead, so I elected to repost the ... "amish" on that group proved to work: ... Please post the COMPLETE query, ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.msde)
  • Re: How To Return A "Range Of Rows"??
    ... SET ROWCOUNT. ... there are no rows returned by your query. ... the proc later tries to set a negative ROWCOUNT. ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: Compare record count in table1 to qty in table2?
    ... Here is a query I'm trying to use and, of course, it does not work: ... INNER JOIN Item_Serial ... Hugo Kornelis, SQL Server MVP ...
    (comp.databases.ms-sqlserver)
  • Re: How To Return A "Range Of Rows"??
    ... Shouldn't I be seeing results with the column headers? ... this just to show that the query works in debug mode? ... Hugo Kornelis, SQL Server MVP ... My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis ...
    (comp.databases.ms-sqlserver)