1. How to fetch all the duplicate records from the table.
2. How to fetch the second highest salary from the table.
Answers were Sorted based on User's Feedback
Answer / kunal gupta
These are the easiest and best queries and 100% tested.
1) 2nd highest salary
select top 1 sal from (Select top 2 sal from emp group by
sal order by sal desc) e order by sal
like wise you can find n highet salar(e.g 5th largest 6th
largest)
select top 1 sal from (Select top [n] sal from emp group by
sal order by sal desc) e order by sal
2) fetch all duplicated records from the table
e.g If table has three columns named 'col1', 'col2'
and 'col3'
select col1, col2, col3 from tbl group by col1, col2, col
having count(col1)>1
Is This Answer Correct ? | 13 Yes | 3 No |
Answer / ashwini
2. Second highest salary from the table
select top 1 salary from emp where salary NOT IN
(SELECT MAX(salary) FROM emp)order by salary desc
Is This Answer Correct ? | 3 Yes | 0 No |
Answer / rajkumar v
Ans:1
select Column_Name from Table_Name group by Column_Name
having count(Column_Name )>1
Ans:2
salary - column name
SELECT TOP 1 salary FROM (SELECT DISTINCT TOP 2 salary FROM
Table_Name ORDER BY salary DESC) A ORDER BY salary
Is This Answer Correct ? | 2 Yes | 0 No |
Answer / amit kumar
(1) Query to fetch 2'nd highest salary:
Select max(sal) from emptable where sal not in (Select
max(sal) from emptable)
(2) How to fetch all the duplicate records from the table.
SELECT COUNT(*), <COLUMN_NAME> FROM EDUCATION GROUP BY
<COLUMN_NAME>
Is This Answer Correct ? | 10 Yes | 9 No |
Answer / rajkumar v
ANS 1:
select p.ColumnName from tablname p group by p.ColumnName
ans2:
Select min(salary)FROM emp WHERE (salary IN (SELECT
TOP 2 salary FROM emp ORDER BY salary DESC))
Or
Select top 1 salary from (select top 2 salary from emp
order by salary desc) emp order by salary
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / saradhi
How to fetch the second highest salary from the table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always greater than one)
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / oracle.hemant
select max(salary) from emp where salary<(select max(salary)
from emp)
Is This Answer Correct ? | 2 Yes | 2 No |
Answer / neelu
select max(salary) from employees where rownum<= 2
order by salary desc
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / sneha s
fetch duplicate records from table:
select column_name from table_name group by column_name having count(column_name)>1;
get the second highest salary:
select distinct salary from emp order by salary desc limit 1,1;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / anbarasan k kanagaraj
select salary from user order by salary desc LIMIT 1, 1
Is This Answer Correct ? | 0 Yes | 1 No |
What are system databases in ms sql server?
What is the difference between char, varchar and nvarchar?
Explain the functionalities that views support?
What is difference between foreign key and unique key?
Are there any preferred steps that need to be taken care of before starting the installation of sql server 2000?
How can count the string ? for ex: If i have string like 'bhaskar' then i need like b:1 h:1 a:2 s:1 k:1 r:1 please give any idea on that
Can we call SP inside a query?
5 Answers CA, CarrizalSoft Technologies, Wipro,
What command is used to delete a table from the database in the sql server and how?
What are security principals used in sql server 2005?
Does union all remove duplicates?
Does dbcc checkdb requires db to be in single_user mode? : sql server database administration
What is normalization?