Home » SQL & PL/SQL » SQL & PL/SQL » Sql Help
Sql Help [message #199645] Wed, 25 October 2006 10:20 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Hi,
I have a table that contains the resultset displayed below in "Current Result" I want to write a query to retrieve distinct users based on TEST_DT
If the TEST_DT is NULL for a user I need that record and ignore the record that may have a TEST_DT.

Here's the script to create the structure.

create table test (EMAIL VARCHAR2(200), FNAME VARCHAR2(80), LNAME VARCHAR2(80), TEST_DT DATE);

INSERT INTO test(email,fname,lname,test_dt)
VALUES
('JOHN.SMITH@YAHOO.COM','John','Smith',NULL)
/
INSERT INTO test(email,fname,lname,test_dt)
VALUES
('MARY.SMITH@YAHOO.COM','Mary','Smith',to_date('10/22/2006','MM/DD/YYYY'))
/
INSERT INTO test(email,fname,lname,test_dt)
VALUES
('MARY.SMITH@YAHOO.COM','Mary','Smith',to_date('10/22/2006','MM/DD/YYYY'))
/
INSERT INTO test(email,fname,lname,test_dt)
VALUES
('MARY.SMITH@YAHOO.COM','Mary','Smith',NULL)
/
INSERT INTO test(email,fname,lname,test_dt)
VALUES
('JOHN.DOE@YAHOO.COM','John','Doe',to_date('10/23/2006','MM/DD/YYYY'))
/

Current Result
----------------

EMAIL FNAME LNAME TEST_DT
JOHN.SMITH@YAHOO.COM John Smith NULL
MARY.SMITH@YAHOO.COM Mary Smith 10/22/2006
MARY.SMITH@YAHOO.COM Mary Smith 10/22/2006
MARY.SMITH@YAHOO.COM Mary Smith NULL
JOHN.DOE@YAHOO.COM John Doe 10/23/2006

Result desired
---------------

EMAIL FNAME LNAME TEST_DT
JOHN.SMITH@YAHOO.COM John Smith NULL
MARY.SMITH@YAHOO.COM Mary Smith NULL
JOHN.DOE@YAHOO.COM John Doe 10/23/2006
Re: Sql Help [message #199653 is a reply to message #199645] Wed, 25 October 2006 10:38 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Thank you very much for providing CREATE TABLE and INSERT sample data statements!

This is an ugly query, but perhaps it'll do what you need (until someone else provides another, clever solution):
SELECT DISTINCT x.email, a.fname, a.lname,
                DECODE (x.cnt_null, 0, a.test_dt, NULL) test_datum
           FROM (SELECT   email,
                          (SELECT COUNT (*)
                             FROM TEST t1
                            WHERE test_dt IS NULL
                              AND t1.email = t.email) cnt_null
                     FROM TEST t
                 GROUP BY email) x,
                TEST a
          WHERE x.email = a.email
Re: Sql Help [message #199667 is a reply to message #199645] Wed, 25 October 2006 12:29 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Agree with littlefoot...wish everyone included the create scripts !!

select email, fname, lname, test_dt from (
	select email, fname, lname, test_dt,
		row_number() over (partition by email order by test_dt nulls first) rn
	from test)
where rn = 1;

[Updated on: Wed, 25 October 2006 12:30]

Report message to a moderator

Re: Sql Help [message #202100 is a reply to message #199667] Wed, 08 November 2006 03:31 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
good one ebrian
Previous Topic: Adding column
Next Topic: Why Primary Key is always selected as an Integer ?
Goto Forum:
  


Current Time: Mon Dec 05 08:55:40 CST 2016

Total time taken to generate the page: 0.10124 seconds