Hi,
I have a table:
CREATE TABLE TEST_RANKING
(
TEST1 VARCHAR2(12 BYTE),
TEST2 VARCHAR2(2 BYTE),
RANK NUMBER(1),
IFDUP NUMBER,
TEST3 VARCHAR2(12 BYTE)
);
and I have these records in it:
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111111', 'EN', 1, 1,'ABCD');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111111', 'EN', 2, 2,'ABfg');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111111', 'EN', 3, 3,'ABCksh');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111112', 'FR', 3, 1,'ABCjus');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111113', 'FN', 3, 1,'ABChust');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111114', 'DE', 4, 1,'ABshid');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111114', 'DE', 5, 2,'soihw');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111115', 'FR', 2, 1,'ABC38jd');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111115', 'FR', 3, 2,'ABCfjid');
Insert into TEST_RANKING
(TEST1, TEST2, RANK, IFDUP,TEST3)
Values
('AB111115', 'FR', 4, 3,'jdush');
COMMIT;
For every record I have a column called IFDUP (which is based on the unique combination of TEST1, TEST2 columns) and if duplicate rows are found, this column will show the number of duplicate rows. So , generally if we have 2 or 3 there it means we have 2 or 3 rows which have the same values in TEST1, TEST2 columns.
There is one more column called RANK which can have different values , let's say from 1 to 5 .
My Question:
How can I chose the record with the lowest ranking when I have a IFDUP which is more than 1 ( when there is a duplicate row there) ?
TEST1 ,TEST2 , RANK, IFDUP , TEST3
AB111115 , FR , 2 ,1 ,ABC38jd
AB111115 , FR , 3 ,2 , ABCfjid
AB111115 , FR , 4 ,3 , jdush
For instance, in the above example , I just want this record to show up in my SELECT statement's result set :
AB111115 , FR , 2 ,1 , ABC38jd
Thanks for the help!
/Hesi