Do You Know How to select the nth(for ex : 10th) largest number in a column without using max() function..
I am using orderid column from orders table in nortwind database for example

SELECT top 1 * FROM(SELECT top 10 orderid FROM orders ORDER BY orderid DESC)a ORDER BY orderid ASC

By : Krishna Prasad J
krishnaprasad.jk@gmail.com

SELECT * FROM tablename orderby salary  LIMIT 1 offset 2

use 3 for third highest 4 for 4th highest after offset

sameerargade@gmail.com

Hi,
You can use the following:

SELECT *
      FROM (SELECT ename
                         ,enumber
                         ,sal
                         ,ROW_NUMBER() OVER(ORDER BY sal DESC) rn
                  FROM emp)
          WHERE rn < 3;

Also, you can use the DENSE_RANK() functionality to get this done in a better way:

SELECT *
      FROM (SELECT ename
                         , sal
                         , Dense_Rank() over(ORDER BY sal DESC
                                                     )rn
                  FROM emp)
      WHERE rn < 3;



Thanks,
vinay singh
Sagarpur New Delhi, India
email- vinay_1020@live.in

i have an employe table. i want display the first ten records.how?

SELECT quantity FROM trade a WHERE 2=(SELECT count(quantity) FROM trade b WHERE a.quantity<=b.quantity);

-hello friend this query is called nested sub query this is the only way to find the values in given count order

You can use following query to find any highest from a table column values:

SELECT MIN(columnName)
FROM tableName  WHERE
columnName IN (SELECT DISTINCT TOP N columnName FROM tableName ORDER BY columnName DESC);

Correlated query:

1.SELECT sal FROM emp e WHERE 1=(SELECT count(DISTINCT(sal)) FROM emp WHERE e.sal<sal);
this gives the exact second heighest value

2.SELECT sal FROM emp e WHERE &(n-1)=(SELECT count(DISTINCT(sal)) FROM emp WHERE e.sal<sal);

this gives the nth heighest value from atable


k.lakshmi_narayana@yahoo.co.in

this is how you can simply do this

SELECT col_name FROM table_name WHERE col_name NOT IN (SELECT col_name FROM (SELECT col_name FROM table_name WHERE col_name IS NOT NULL ORDER BY col_name DESC)
WHERE rownum<=2)ORDER BY col_name

you can use this and this is general you can find any highest value

SELECT min(sal) FROM (SELECT sal FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC) WHERE rownum<=&rownum

SELECT salary FROM(
    SELECT a1.salary,count(a2.salary)sal
    FROM salarytable a1,salarytable a2
    WHERE a1.salary<=a2.salary OR (a1.salary=a2.salary)
    GROUP BY a1.salary)a WHERE sal=4


Here 4 specifies Max salary at fourth position