On Thu, 11 Aug 2005 10:30:02 -0700, Jig Bhakta wrote:

>Hi,
>
>I have a query that returns back rows that have multiple entries for a given
>UniqueID. I want the query to be expanded so that it then only returns 1 of
>the multiple rows based on the maximum date. Thefore, my return set looks
>like this:
>
>UniqueID Name Date
>---------------------------------
>123 ABC 20041104
>123 ABC 20041105
>456 ABC 20031221
>456 ABC 20031222
>789 ABC 20050430
>789 ABC 20050429
>
>The query is this:
>
>select *
>from table A
>where (select count(*) from table where UniqueID = A.UniqueID) > 1

"Hugo Kornelis" wrote:

> On Thu, 11 Aug 2005 10:30:02 -0700, Jig Bhakta wrote:
>
> >Hi,
> >
> >I have a query that returns back rows that have multiple entries for a given
> >UniqueID. I want the query to be expanded so that it then only returns 1 of
> >the multiple rows based on the maximum date. Thefore, my return set looks
> >like this:
> >
> >UniqueID Name Date
> >---------------------------------
> >123 ABC 20041104
> >123 ABC 20041105
> >456 ABC 20031221
> >456 ABC 20031222
> >789 ABC 20050430
> >789 ABC 20050429
> >
> >The query is this:
> >
> >select *
> >from table A
> >where (select count(*) from table where UniqueID = A.UniqueID) > 1
>
> Hi Jig,
>
> Try
>
> SELECT UniqueID, Name, [Date]
> FROM [table] AS A
> WHERE EXISTS
> (SELECT *
> FROM [table] AS B
> WHERE B.UniqueID = A.UniqueID
> AND B.[Date] > A.[Date])
> (untested)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)