standard sql query

[samit]

Hello Everybody,
I face a problem to
SELECT the paticular customer record from 50 records in database whose salary is 15 th heighest with out using 'limit' keyword in mysql or 'rownum' in oracle.
query should be in standard SQL ,which will run in all oracle,mysql,and in sql server also.
please try.................

[amitpatel66]

Hello Everybody,
I face a problem to
SELECT the paticular customer record from 50 records in database whose salary is 15 th heighest with out using 'limit' keyword in mysql or 'rownum' in oracle.
query should be in standard SQL ,which will run in all oracle,mysql,and in sql server also.
please try.................

Please post what you have tried for my reference!!

[samit]

Please post what you have tried for my reference!!

I have tried the following queries
the following query runs in mysql to treat name of the employee of 30 th heighest salary.
SELECT name FROM SALARY_TABLE ORDER BY SALARY DESC LIMIT 29,1
the following query runs in oracle to treat name of the employee of 30 th heighest salary.
SELECT ROWNUM, name FROM SALARY_TABLE ORDER BY SALARY DESC where ROWNUM=30;

But I like to write a common query to run in both. I face this question in some interview table

[amitpatel66]

I have tried the following queries
the following query runs in mysql to treat name of the employee of 30 th heighest salary.
SELECT name FROM SALARY_TABLE ORDER BY SALARY DESC LIMIT 29,1
the following query runs in oracle to treat name of the employee of 30 th heighest salary.
SELECT ROWNUM, name FROM SALARY_TABLE ORDER BY SALARY DESC where ROWNUM=30;

But I like to write a common query to run in both. I face this question in some interview table

try this:

 
SELECT ename,g.msal FROM (select MIN(salary) msal from emp e where 30 > (SELECT count(*) from emp where salary > e.salary)) g,emp d
WHERE d.salary = g.msal
 
 

The above query will give you the enames of all the employees whose salary is 30th highest.

It will return all names if two employees have same salary at 30th position!!

[samit]

try this:

 
SELECT ename,g.msal FROM (select MIN(salary) msal from emp e where 30 > (SELECT count(*) from emp where salary > e.salary)) g,emp d
WHERE d.salary = g.msal
 
 

The above query will give you the enames of all the employees whose salary is 30th highest.

It will return all names if two employees have same salary at 30th position!!

*************************reply******************** ********************
thanks a lot for sending this query. this is running fine

[amitpatel66]

*************************reply******************** ********************
thanks a lot for sending this query. this is running fine

You are Welcome :)

Regards,
Amit