Identify required row from group 1

[josees]

Is there a way using SQL to extract only the following? My status is being pulled from another table that contains historical status information for this member. Any thoughs or ideas?

ID NAME STATUS
1232 JOHN DOE UNEXAMINED
1232 JOHN DOE EXAMINED
5555 JANE DOE UNEXAMINED
1232 JANE SMITH EXAMINED
9999 TOM JONES UNEXAMINED
9999 TOM JONES EXAMINED

Expected Results:
ID NAME STATUS
1232 JOHN DOE EXAMINED
5555 JANE DOE UNEXAMINED
1232 JANE SMITH EXAMINED
9999 TOM JONES EXAMINED


Thanks for any guidance!

 

[SantaMufasa]

Josees,

Welcome to both Tek-Tips and to the Oracle forums.

Your sample data show both "JOHN DOE" and "JANE SMITH" with the same ID. Is that an actual situation that you want the code to deal with, or is that simply "sample-data error"? (For the sake of sample code, I'll presume that "JANE SMITH" has an ID of 1233. )

select * from josees;

  ID NAME       STATUS
---- ---------- ----------
1232 JOHN DOE   UNEXAMINED
1232 JOHN DOE   EXAMINED
5555 JANE DOE   UNEXAMINED
1233 JANE SMITH EXAMINED
9999 TOM JONES  UNEXAMINED
9999 TOM JONES  EXAMINED

select id, name, min(status) status
  from josees
 group by id, name;

  ID NAME       STATUS
---- ---------- ----------
1232 JOHN DOE   EXAMINED
1233 JANE SMITH EXAMINED
5555 JANE DOE   UNEXAMINED
9999 TOM JONES  EXAMINED

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[josees]

You are correct about my typo, thanks! This works for exactly what I need, thanks again!!!!