From the table i want everything highlighted with a *
I wanted an SQl expression to look at values in Column 1 (ID), look at
the corresponding values in the second column (F1) and select the row
with the highest value, and then if there was more than one row for
that ID with the same value in F1, look at column 3 (F2) and select
the row with the highest value in this column.
SQL> select * from test;
ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
1 11 1
1 9 12
2 3 5
* 2 9 13
2 9 9
3 1 2
3 1 1
* 3 7 5
I try the following
SQL> select * from test t1
2 where f1 = (select max(f1) from test t2 where t2.id = t1.id)
3 ;
And get the following
ID F1 F2
---------- ---------- ----------
1 12 4
* 1 12 6
* 2 9 13
2 9 9
* 3 7 5
If I add another line with an AND statement after line 2 I either get
nothing or rubbish as the output.
Ideas?
Cheers