แสดง TOP (จำนวน) อันดับแรกในออราเคิล (TOP-N Analysis query)

มีหลายครั้ง ที่เราต้องการแสดง ข้อมูล เรียงลำดับ TOP เช่น แสดง อันดับเพลงฮิต 10 อันดับแรก ถ้าเป็น MS SQL Server หรือ My SQL มี Syntax ง่ายๆ มารองรับอยู่แล้ว เช่น

MS SQL SERVER :

SELECT TOP 10 vote , music_name
FROM  music
ORDER BY  vote  DESC    

My SQL :

SELECT  vote , music_name
FROM   music
ORDER BY vote DESC
LIMIT 10 ;                     

แต่ สำหรับ Oracle นั้น ไม่มี Syntax ง่ายๆ แบบนี้มารองรับครับ .. ไม่รู้ทำไม ..ถ้าผมเป็นเจ้าของบริษัท Oracle คงสั่งให้ลูกน้อง ทำ Syntax TOP เหมือน MS SQL Server ไปแล้ว แต่พอดีผมไม่ใช่ เจ้าของบริษัท Oracle ... โปรแกรมเมอร์ Oracle เลยต้องทนลำบากต่อไป สำหรับ Oracle Database นั้น ถ้าจะเขียน TOP อันดับ ต้องใช้ Sub-Query และ คอลัมน์อีแอบ (Pseudocolumn) ชื่อว่า ROWNUM เข้าช่วยครับ เรียวิธีการนี้ว่า TOP-N Analysis

ใน ข้อมูลทุกตารางนั้น จะมีคอลัมน์อีแอบ อยู่ชื่อว่า ROWNUM Pseudocolumn ซึ่งเป็นหมายเลขของแต่ละแถวในตาราง ที่ผมเรียกว่า คอลัมน์อีแอบ นั้น เพระาปรกติ เวลาคุณใช้คำสั่ง DESC หรือ DESCRIBE ชื่อตาราง มันจะไม่แสดงชื่อคอลัมน์นี้ออกมาด้วย แต่ในการ SELECT คุณบอกให้มันแสดงตัวตนออกมาได้

ตัวอย่างแรก ผมจะแสดง ชื่อ และ เงินเดือน พนักงานที่มีเงินเดือนมากที่สุด 3 อันดับแรก ในบริษัท (เงินเดือนเรียงจาก มาก ไปน้อย)

SELECT ROWNUM as RANK, ename , sal
FROM (SELECT ename,sal FROM emp
    ORDER BY sal DESC)
WHERE ROWNUM <= 3; 

ตัวอย่างอันสุดท้ายนี้ ผมจะแสดง ชื่อ และ วันที่จ้างงานเข้ามา ของพนักงานที่มีอายุงานอาวุโสมากที่สุด 4 อันดับแรก ของบริษัท
( อาวุโส แสดงว่า จ้างงานมาในอดีตที่ไกลที่สุด ดังนั้น วันที่จ้างงาน ต้องเรียงจาก น้อย ไปมาก )

SELECT ROWNUM as SENIOR , e.ename,e.hiredate
FROM (SELECT  ename,hiredate FROM  emp
    ORDER BY hiredate) e
WHERE ROWNUM<=4 111111111

ที่มา http://www.oracleskill.com/oracle-tutorials/TOP-N-Analysis.html