I have a table XYZ as
+------+------+------+-----+ | X1 | x2 | x3 | x4 | +------+------+------+-----+ | a | b | c | 1 | | a | b | d | 2 | | p | q | e | 3 | | p | q | f | 4 | +------+------+------+-----+
condition is if x1 and x2 matches then get x3 and x4 of row where x4 is maximum when i will query this table I want to get output as
+------+------+------+-----+ | X1 | x2 | x3 | x4 | +------+------+------+-----+ | a | b | d | 2 | | p | q | f | 4 | +------+------+------+-----+
i tried as select * from XYZ t1, (select x1,x2,max(x4) from XYZ ) t2 where t1.x1=t2.x1 and t1.x2=t2.x2
but i want to use advance functions like rank and partations as this is much slow in large database
rank or row_number can be used depending on whether you want to return multiple rows in the case of x4 being equal.
eg:
SQL> select *
2 from (select x1, x2, x3, x4, rank() over (partition by x1, x2 order by x4 desc) rnk
3 from xyz)
4 where rnk = 1;
X X X X4 RNK
- - - ---------- ----------
a b d 2 1
p q f 4 1
but if for a, b we had two rows at x4=2, then we'd get two rows back:
SQL> insert into xyz values ('a', 'b', 'd', 2);
1 row created.
SQL> select *
2 from (select x1, x2, x3, x4, rank() over (partition by x1, x2 order by x4 desc) rnk
3 from xyz)
4 where rnk = 1;
X X X X4 RNK
- - - ---------- ----------
a b d 2 1
a b d 2 1
p q f 4 1
if you wanted just one, use row_number() instead:
SQL> select *
2 from (select x1, x2, x3, x4, row_number() over (partition by x1, x2 order by x4 desc) rn
3 from xyz)
4 where rn = 1;
X X X X4 RN
- - - ---------- ----------
a b d 2 1
p q f 4 1
SQL>