|
Home > Archive > MS SQL Server MSEQ > August 2005 > Query returning multiple rows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Query returning multiple rows
|
|
| Jig Bhakta 2005-08-11, 1:26 pm |
| 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 2005-08-11, 8:24 pm |
| 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)
| |
| Vishal Parkar 2005-08-12, 11:24 am |
| 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 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)
>
|
|
|
|
|