top - sql select record with greatest value




從表中找出第n個最高工資 (10)

一般查詢所有數據庫

SELECT DISTINCT salary FROM emp X WHERE n = 
    ( SELECT COUNT(DISTINCT salary) FROM emp WHERE salary >=X.salary )

用給定的數字替換n。 例如拿到第三高的薪水

SELECT DISTINCT salary FROM emp X WHERE 3 = 
( SELECT COUNT(DISTINCT salary) FROM emp WHERE salary >=X.salary )

要么

在任何編程語言

從工資中選擇*

然後用編程語言(JAVA,.net或php)迭代結果集,

Mysql的

SELECT DISTINCT salary FROM emp X order by salary desc limit n,1
name   salary
-----   -----
mohan     500
ram      1000
dinesh   5000
hareesh  6000
mallu    7500
manju    7500
praveen 10000
hari    10000

如何使用Oracle從上述表格中找到第十高的薪水?


你可以在google上找到很多東西

select * 
  from table_name T1 
 where Nth = (select count(distinct (T2.sal)) 
                from table_name T2 
               where T1.sal <= T2.sal )

select * 
  from ( select s.*, rank() over (order by salary desc) as rownumber
           from salary )
 where rownumber = nth

把你的工資號碼代替“nth”


以簡單的方式選擇第n個最高薪水

SELECT emp_no, sal
FROM
(
select emp_no, sal, ROW_NUMBER() OVER (order by sal desc) RN 
from emp 
order by sal desc
)
WHERE RN = n;

其中n =你想要的第n個數字.....


select * from 
(
    select sal, rank() over (order by sal DESC/ASC) rnk 
    from emp
) 
where rnk = 1/2/3/4/5/6/...;

請參閱以下查詢獲得第n個最高工資。 通過這種方式,你可以得到第十高的薪水。 如果你想獲得第n個最低工資,只需要在查詢中用ASC代替DESC。


在MySql中,運行下面的SQL查找第n個最高工資:

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary desc limit N-1,1;

例如查找第三高薪:

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary desc limit 2,1;

例如查找第三最低工資(按“按順序排列”):

SELECT distinct(salary), emp_id, name 
       FROM `emp_salary` 
       group by salary 
       order by salary asc limit 2,1;

在Sql server 2012及以上。 請參閱此鏈接獲取,偏移,Sql服務器頁面

 Use AdventureWorks /* AdventureWorks 2014 DB*/

Select distinct(CommissionPct) from Sales.SalesPerson
order by CommissionPct desc OffSet 3 Rows Fetch next 1 Rows only

--This for 4Th highest value (N-1)

看這裡


在Oracle中嘗試以下內容:

SELECT *
FROM
  (SELECT rownum AS rn,
    a.*
  FROM
    (WITH DATA AS -- creating dummy data
    ( SELECT 'MOHAN' AS NAME, 200 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'AKSHAY' AS NAME, 500 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'HARI' AS NAME, 300 AS SALARY FROM DUAL
    UNION ALL
    SELECT 'RAM' AS NAME, 400 AS SALARY FROM DUAL
    )
  SELECT D.* FROM DATA D ORDER BY SALARY DESC
    ) A
  )
WHERE rn = 3; -- specify N'th highest here (In this case fetching 3'rd highest)

乾杯!


我們可以通過相關的子查詢來做到這一點。

SELECT Salary
FROM Employee E1
WHERE N-1 = (SELECT COUNT(*)
         FROM Employee E2
         WHERE E1.salary <E2.Salary) 

有關進一步的知識,請檢查這個鏈接.. 相關的子查詢與例子





greatest-n-per-group