Answer / manshul

select * from employee e, (select salary, rownum rn from
(select distinct salary from employee order by salary desc))
T where T.sal= e.sal and T.rn = 4

Answer / shiva

SELECT MAX( salary) FROM salary where salary not in(
SELECT DISTINCT TOP 3 salary FROM salary order by salary desc)

Answer / vidit

DECLARE @SQL VARCHAR(2000), @N INT
SET @N = 5
SET @N = @N - 1

SET @sql = 'select top 1 salary from ABC where salary not
in ( SELECT TOP ' + CAST(@n AS VARCHAR(100)) + ' salary
FROM ABC order by salary desc ) ' + ' order by salary desc '

SELECT @SQL
EXEC @SQL

Answer / anonymous

select min(salary) from(
select salary from test
order by salary desc
)
where rownum<4



For ORACLE 8i,9i,10g


select distinct salary from
(select salary,
dense_rank() OVER (PARTITION BY empname ORDER BY salary
desc) rank
from test) where rank=7
;

Answer / dewashish kumar pal

select salary from tablename t where (n-1)= (select count(*) from tablename where salary > t.salary);


note:- t = tablename

Answer / vishal patel

with cte as (
SELECT ROW_NUMBER() OVER(PARTITION BY ecc_dm_id_dep ORDER
BY ecc_gross_simple DESC) AS RowID,
*
FROM emp_curr_company
)
SELECT *
FROM cte
WHERE RowID = 2

Answer / palash

question is which db are we looking at..
sqlserver or oracle

if sqlserver then top function is readily available to get
the nth highest sal etc

what if it the db is oracle, oracle does not have a
implicit top function to use so how do we go about it

couple of ways

1) use analytical queries
2) use co-related queries (suitable in small sized
databases)

1) analytical queries

if looking for nth highest within the complete table

select * from (select sal , dense_rank() over(order by sal
desc) rnk from emp ) where rnk = n

we can use row_number/rank functions also in place of
dense_rank.


if looking for nth highest within each department.

select * from (select sal, dense_rank() over (partition by
dept order by sal) rnk from emp) where rnk = n


2) co-related queries:

select sal from emp e1 where (n-1) = (select count(1) from
emp e2 where e2.sal > e1.sal)

this query will be pretty slow if the size of the table is
huge.

so my advice is to use the analytical version which is much
much faster than the co-related version.

Answer / dewashish kumar pal

Select max(salary) from tablename where salary not in (select max(salary) from tablename);

Answer / abhishek singh

SELECT TOP 1 salary
FROM dbo.emp
WHERE (salary IN
(SELECT DISTINCT TOP n salary
FROM dbo.emp
ORDER BY salary DESC))
ORDER BY salary

Answer / gopi mandadi

Select emp_no,name,dept,sal from emp_t A
Where &n = (Select sal from emp_t B Where A.sal <= b.sal)

CREATE TABLE [dbo].[HPMS_CompetencyTypes](CompetencyType varchar(50) ) go create trigger hpms_create_Insert on HPMS_CompetencyTypes for insert as if Exists ( select * from [HPMS_CompetencyTypes] where CompetencyType=(select * from [HPMS_CompetencyTypes])) begin Rollback tran Raiserror ('duplicate value',12,6) go insert HPMS_CompetencyTypes (CompetencyType) values ('new') I'm new to trigger can any one tell me where is the issue. Please.

2 Answers  

How to view existing indexes on an given table using sys.indexes?

0 Answers  

Do you know what are various aggregate functions that are available?

0 Answers  

what is a join and explain different types of joins? : Sql server database administration

0 Answers  

how do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? : Sql server database administration

0 Answers  

what is the system function to get current user's user id? : Sql server database administration

0 Answers  

What are the new features in sql server 2016?

0 Answers  

What is the use of toad or sqldbx.?

0 Answers   MCN Solutions,

How do you load large data to the SQL server database?

2 Answers  

what is the difference between writing data to mirrored drives versus raid5 drives. : Sql server administration

0 Answers  

Does a full backup include transaction log?

0 Answers  

I have a table emp , Fields with empname,dnname,dno,salary. now I want copy distinct salary with all emp detail from emp into new table which is not already exist in database. how would I do this ?

2 Answers   Techno Labs,