SQL & PL/SQL

Hi,

I created the following resultset from a UNION of two select statements.


Payday(CHAR) ID HIRE_DATE SALARY
---------------------- --------- ------------------ -----------------
8/1/2003 13 199903 $70,000.00
NO PAY 25 199903 $180,000.00
10/2/2000 43 200201 $80,000.00
NO PAY 22 200201 $20,000.00
2/2/2006 56 200603 $100,000.00
2/1/2007 45 200612 $500.00
2/1/2008 22 200712 $300,000.00

What I would like to do is to create the resul set below from the one above

Payday(CHAR) ID HIRE_DATE SALARY
---------------------- --------- ------------------ -----------------
NO PAY 25 199903 $180,000.00
NO PAY 22 200201 $20,000.00
2/2/2006 56 200603 $100,000.00
2/1/2007 45 200612 $500.00
2/1/2008 22 200712 $300,000.00

The goal is to select the record with NO PAY when the hiredate is the same.

I am not sure how to accomplish this. I tried to do a CASE statement to count the hire date and select one when count is greater within a select statement using this resultset as a table and that didn't work. I tried the case statement in the where clause and it still did not work.

Please help me as I have exhausted everything.

Thank you so much!!!
I move the CASE