Re: Query returning multiple rows

a small correction, to get the max date:

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]) --CHANGE HERE


"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)
>
.

Relevant Pages

  • Re: Query returning multiple rows
    ... >I have a query that returns back rows that have multiple entries for a given ... >UniqueID Name Date ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Query returning multiple rows
    ... I have a query that returns back rows that have multiple entries for a given ... UniqueID Name Date ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Re: sequential query record numbering
    ... not sequential in my query. ... >> navigator. ... >> uniqueid to work with. ... Prev by Date: ...
    (microsoft.public.access.queries)