How to restrict results where duplicate in one column but not another

[slicendice]

Sorry if the subject doesn't explain things too well, but it's difficult to get across in one line! Anyway, we have a query which returns a data set that contains two columns - an ID and optionally a date. The query used to generate the data is as follows:

WITH evt AS (SELECT aceu_cnh_id, 
                    aceu_row_count 
             FROM   txtu_actevent),
     rec AS (SELECT acru_cnh_id, 
                    COUNT(*) AS rec_count 
             FROM   txtu_actrec 
             GROUP BY acru_cnh_id)
SELECT evt.aceu_cnh_id, 
       NULL AS newest_date
FROM   evt, 
       rec
WHERE  evt.aceu_cnh_id = rec.acru_cnh_id
AND    evt.aceu_row_count <> rec.rec_count
UNION
SELECT aceu_cnh_id AS cnh_id, 
       NULL        AS newest_date
FROM   vwe_txtu_actevent
WHERE  aceu_cnh_id NOT IN (SELECT acru_cnh_id
                           FROM   vwe_txtu_actrec)
UNION
SELECT achu_cnh_id AS cnh_id, 
       NULL        AS newest_date
FROM   vwe_txtu_acthud
WHERE  achu_cnh_id NOT IN (SELECT aceu_cnh_id
                           FROM   vwe_txtu_actevent)
UNION
SELECT ch.cnh_id AS cnh_id, 
       GREATEST(ch.cnh_amended_on,
                ah.achu_amended_on,
                ar.acru_amended_on) AS newest_date
FROM   txt_cnote_head ch,
       txtu_acthud    ah,
       txtu_actrec    ar
WHERE  ah.achu_cnh_id = ch.cnh_id
AND    ar.acru_cnh_id = ch.cnh_id
AND    GREATEST(ch.cnh_amended_on,
                ah.achu_amended_on,
                ar.acru_amended_on) >= SYSDATE - 10
ORDER BY 1, 2 NULLS LAST;

In the data set that is returned, an ID may appear just once, or it may appear twice - once with a date and once without; so for example:

ID	DATE
----	-----------
1000	10-JUN-2013
1015	15-AUG-2013
1015	<NULL>
1019	<NULL>
1025	11-DEC-2013
1025	<NULL>
1031	<NULL>
1033	22-DEC-2013
1033	<NULL>
1058	04-JAN-2014

What I want to do, if possible, is restrict the data set such that each ID only appears once, with it's associated date (if one exists). So for the above data set, what I want instead is:

ID	DATE
----	-----------
1000	10-JUN-2013
1015	15-AUG-2013
1019	<NULL>
1025	11-DEC-2013
1031	<NULL>
1033	22-DEC-2013
1058	04-JAN-2014

Is this possible?

Thanks

 

[johnherman]

You could always write the results of this query to a temp table and then just read the temp table....yes, I know it's not as "slick" as doing it all in just one query.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[LKBrwnDBA]

What about using MAX() function?:

WITH evt AS (SELECT aceu_cnh_id, 
                    aceu_row_count 
             FROM   txtu_actevent),
     rec AS (SELECT acru_cnh_id, 
                    COUNT(*) AS rec_count 
             FROM   txtu_actrec 
             GROUP BY acru_cnh_id)
SELECT aceu_cnh_id, 
       MAX(newest_date) newest_date
  FROM (
SELECT evt.aceu_cnh_id, 
       NULL AS newest_date
FROM   evt, 
       rec
WHERE  . . .
. . .   E t c   . . .
. . .  ah.achu_cnh_id = ch.cnh_id
AND    ar.acru_cnh_id = ch.cnh_id
AND    GREATEST(ch.cnh_amended_on,
                ah.achu_amended_on,
                ar.acru_amended_on) >= SYSDATE - 10 
)
GROUP BY aceu_cnh_id
ORDER BY 1, 2 NULLS LAST;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb