Home » SQL & PL/SQL » SQL & PL/SQL » How to filter multiple records (10 g)
How to filter multiple records [message #434312] Tue, 08 December 2009 09:22 Go to next message
KTZ
Messages: 15
Registered: January 2005
Junior Member
I have an employee table with employee last name and status as the fields needed for this discussion. Some employees only have one record, some may have more than one record in the table, but with different status codes - T, N, D, Y, etc.

I would like to have one SQL statement to get one record for each employee based on a hierarchy of the status codes listed above. Example if Employee has a T record take that, if not, then look for the N record, Then the D record, then the Y record and so on until eather a record is found or the list of viable status codes is exhausted for that employee.

CREATE TABLE Emp
(ID NUMBER primary key not null,
Name VARCHAR2(25),
Status VARCHAR2(1));


insert into Emp values(1,'Davis', 'T');
insert into Emp values(2,'Davis', 'Y');
insert into Emp values(3,'Smith', 'N');
insert into Emp values(4,'Johnson', 'D');
insert into Emp values(5,'Brown', 'R');
insert into Emp values(6,'Brown', 'Y');
insert into Emp values(7,'Thomas', 'Y');
insert into Emp values(8,'Thomas', 'N');


Selecting records using this status code order - T, N, D, Y, R would yield
the following results set:

Davis, 'T'
Smith, 'N'
Johnson, 'D'
Brown, 'Y'
Thomas, 'N'

I'm pretty sure I need an analytical function like rank, with a rownum, but I'm not sure.

Thanks,

Kim
Re: How to filter multiple records [message #434314 is a reply to message #434312] Tue, 08 December 2009 09:39 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
PretQuote:
I'm pretty sure I need an analytical function like rank, with a rownum, but I'm not sure.

Close, just a rank will be fine, use a decode such as
DECODE(status, 'T', 1, 'N', 2, etc) 

as your ORDER BY in the RANK function call
Re: How to filter multiple records [message #434326 is a reply to message #434314] Tue, 08 December 2009 12:31 Go to previous message
KTZ
Messages: 15
Registered: January 2005
Junior Member
Perfect, thanks!

SELECT  ID, name, Status, StatusRank
FROM   (SELECT   ID, name, Status,RANK ()
        OVER (partition by ID ORDER BY DECODE {status,'T',1,'N',2,'D',3,'Y',4,))StatusRank
FROM   emp
Previous Topic: best way to optimise Delete syntax
Next Topic: FTP package by Tim Hall
Goto Forum:
  


Current Time: Sat Dec 10 20:36:22 CST 2016

Total time taken to generate the page: 0.04006 seconds