1 the query to find the top 2nd highest salary is below:
select top 1* from Employee where Salary not in
(select max(Salary) from Employee) order by Salary desc
2 you can get the fifth or n
th highest salary of Employee table by using this statement.
方法1 ：Select Min(Salary) From Employee Where Salary In
(
Select Top(5) Salary
From Employee
Order By Salary DESC
)
方法2 ：
select max(salary) from emp where salary not in(select top(n-1) salary from emp order by salary desc）;
注意：The ORDER BY clause is invalid in
views, inline functions, derived
tables, subqueries, and common table
expressions, unless TOP or FOR XML is
also specified.
3 how to delete duplicate rows in table having rowid consider the table employe table with rowid,name and email .
delete from tblemploye where rowid not in
( select max(rowid) from tblemploye group by name, email having count(*)>1)
本文介绍了如何使用SQL查询来找出第二高的薪资、第五高的薪资，并展示了如何删除具有重复rowid的记录，同时还提供了查找数据库中所有表的方法。
扫一扫
到【灌水乐园】发言
1 the query to find the top 2nd highest salary is below:
select top 1* from Employee where Salary not in
(select max(Salary) from Employee) order by Salary desc
2 you can get the fifth or n th highest salary of Employee table by using this statement.
方法1 ： Select Min(Salary) From Employee Where Salary In
(
Select Top(5) Salary
From Employee
Order By Salary DESC
)
方法2 ：
select max(salary) from emp where salary not in(select top(n-1) salary from emp order by salary desc）;
注意： The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
3 how to delete duplicate rows in table having rowid
consider the table employe table with rowid,name and email .
delete from tblemploye where rowid not in( select max(rowid) from tblemploye group by name, email having count(*)>1)4 find all tables in a database
Select * from sysobjects where xtype = ‘u’
5