Re: Query returning multiple rows

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

  • 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: Query returning multiple rows
    ... SELECT UniqueID, Name, ... "Hugo Kornelis" wrote: ... >>I have a query that returns back rows that have multiple entries for a given ...
    (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)
  • Re: listService.GetListItems
    ... Looks like you need to restructure your query to use "Or" commands to get ... the multiple entries. ... I am using web service to retrieve items from a list. ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: Limiting List from Table Field
    ... I do my best to try and figure something out but the help files are useless ... Thank you so much for helping me with this query. ... > Now in your form design, use this query as the rowsource to your listbox ... I need multiple entries in the table but not to ...
    (microsoft.public.access.gettingstarted)