INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

CODE

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:

CODE

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:

CODE

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

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

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


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

What about using MAX() function?:

CODE

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; 
3eyes

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close