SELECT QUERY

[jasonhuibers]

How can I query the following examples. I want to SELECT WHERE in the case there is a duplicate acct id - return the record with a value in Expire_DT.
Also take into consideration, if there is a duplicate acct id as in example 2 with no Expire_DT use a DISTINCT to return only one record..

Example 1
Acct_ID LName FName Expire_DT
12345 Smith John 01-May-2012
12345 Smith John

Example 2
Acct_ID LName FName Expire_DT
22222 Brown Larry
22222 Brown Larry

 

[SantaMufasa]

Jason,

(Please help us help you by posting CREATE TABLE and INSERT INTO statements with your question.)

Here are data and a solution that, I believe, does what you want:

select * from acct;

ACCT_ID LNAME           FNAME           EXPIRE_DT
------- --------------- --------------- ---------
  12345 Smith           John            01-MAY-12
  12345 Smith           John
  22222 Brown           Larry
  22222 Brown           Larry
  33333 Obama           Barak           02-NOV-12
  44444 Romney          Mitt            02-NOV-12

select Acct_ID, LName, FName, max(Expire_DT) Expire_DT
  from acct
 group by Acct_ID,LName,FName
having count(*) > 1
/

ACCT_ID LNAME           FNAME           EXPIRE_DT
------- --------------- --------------- ---------
  12345 Smith           John            01-MAY-12
  22222 Brown           Larry

Let us know if you agree that this does what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.