Searching for 2 records which share the same column flag 1

[shanyaj]

Hello,
I am newbie for PL/SQL.

I am trying to create a script/procedure to do the following:
Sample table:
Flag VARCHAR2(1)
JOHN VACHAR2(10)
SUSAN VARCHAR2(12)
DATE_ENTERED DATE;

The Flag will have a value of Y or N
There are duplicate records in the table.

I need to create a PL/SQL procedure that will extract 1 single record which has a value of Y with the "most recent date" entry for JOHN and similarly 1 record for SUSAN.

Thus, there will be two respective records for JOHN and SUSAN with the most recent enry with a flag of Y.

Please help!
shanyaj

 

[Thargy]

Shanyaj,

can you tell us what the assignment says to do? Do you have any sample data?

Regards

T

 

[SantaMufasa]

...and posting what you have come up with on your own will help us to help you, as well, Shanyaj.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[shanyaj]

The procedure is required to retrieve the most recent record that was updated for JOHN as well as SUSAN with a Flag 'Y'. Note: there may be several records with 'Y' and 'N' values. We need to find the latest entry for each of them.

So the Procedure will locate 1 lastest record for JOHN and 1 lates Record for Susan with flag 'Y'.

Hope this clarifies little bit better.
Shanyaj

 

[SantaMufasa]

Hope this clarifies little bit better.

Only slightly...So, if, in addition to John and Susan, you had the addition of 120 additional employees that you needed to track. Do you add 120 more columns to the table? And if you do, what will be the length of each column (since John is length "10" maximum, and Susan is length "12" maximum)? I'm bringing this up because the data seem badly denormalised, and before we suggest a solution, we should get the data right for your business (unless, of course, this is a classroom assignment...Yes?)


Also, you have not posted these yet:[ul][li]Sample "CREATE TABLE..." statement and sample "INSERT INTO..." (sample data).[/li][li]Your most recent "SELECT..." attempt that doesn't seem to work.[/li][/ul](I.e., we just don't want people to start thinking of us as a "Free Consulting Service" <grin>).


Not trying to be difficult here, but just trying to be reasonable.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[shanyaj]

Let me make this simpler to understand.
Flag VARCHAR2(1)
DATA_COL1 VACHAR2(10)
DATA_COL2 VARCHAR2(12)
DATE_ENTERED DATE;


Again: I can't get any simpler then this.
There is constantly data being populated to this Table. It could have million records with several dates and several flags 'Y' or 'N'

I need to know the script that will extract the latest record that was populated for DATA_COL1 with a flag 'Y' and similarly for DATA_COL2.

Hence I will have extracted 2 records from this Table with the given criteria.

shan

 

[SantaMufasa]

Again: I can't get any simpler then this.

I'm sorry to ask for help, Shan...you see, I'm just a very simple-minded character. The way to make it simpler for me to help you is, as Thargy and I requested above: if you coule post the "CREATE TABLE..." and "INSERT INTO..." code.


Since you didn't post what we requested, we needed to do that.

Here are some table data and solution code:

select * from shanyaj;

FLAG DATA_COL1 DATA_COL2 DATE_ENTERED
---- --------- --------- ------------
Y    A                   03-APR-11
N    C                   04-APR-11
Y    E                   05-APR-11
N    B                   06-APR-11
Y              D         07-APR-11
N              F         08-APR-11
Y              G         09-APR-11
N              H         10-APR-11

select *
  from shanyaj
 where flag = 'Y'
   and data_col1 is not null
   and date_entered = (select max(date_entered)
                         from shanyaj
                        where data_col1 is not null
                          and flag = 'Y')
 union
select *
  from shanyaj
 where flag = 'Y'
   and data_col2 is not null
   and date_entered = (select max(date_entered)
                         from shanyaj
                        where data_col2 is not null
                          and flag = 'Y')
/

FLAG DATA_COL1 DATA_COL2 DATE_ENTERED
---- --------- --------- ------------
Y    E                   05-APR-11
Y              G         09-APR-11

There are multiple ways to solve this issue...above is one of the ways.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[shanyaj]

Sincerely appreciated, SantaMufas.

Sorry, for not putting the sample dataset together. Next time i know better.

shan

 

[KenCunningham]

It is customary, shanyaj, to express your sincere appreciation to contributors who have assisted in solving your problem by awarding a little purple star in these fora.

The internet - allowing those who don't know what they're talking about to have their say.

 

[bartha]

Thanks for advice KenCunnigham.

I just did so.

shanyaj