Friday, June 5, 2009

İkinci en Yüksek değeri hesaplama

1) SELECT MAX(value) FROM table WHERE value < (SELECT MAX(value) FROM table)
2) SELECT value FROM (SELECT value, RANK() OVER (ORDER BY value DESC) rn FROM table) WHERE rn = 2
3) SELECT value FROM (SELECT value, DENSE_RANK() OVER (ORDER BY value DESC) rn FROM table) WHERE rn = 2
4) SELECT value FROM (SELECT value, ROW_NUMBER() OVER (ORDER BY value DESC) rn FROM table) WHERE rn = 2
5) SELECT value FROM (SELECT ROWNUM, value FROM (SELECT value FROM table ORDER BY value DESC)) WHERE rn = 2
6) SELECT value FROM (SELECT ROWNUM, value FROM (SELECT DISTINCT value FROM table ORDER BY value DESC)) WHERE rn = 2
ikinci en yüksek değeri bulmak için yukaridaki yöntemleri izleyebiriz. deneme amaçli olarak aşağıda bir örnek var


Values 5,5,3,3,2

Your goal is:

3 : use solution 1,6
3,3 : use solution 3
5 : use solution 4,5
Nothing (as there are 2 highest and 1 third highest): Use solution 2

No comments: