Top-N queries เป็นวิธีการดึงข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกออกมาจากตาราง โดยวิธีการดึงข้อมูลแบบ Top-N นั้นมีได้หลายวิธี แต่ในบทความนี้จะนำเสนอวิธีการดึงข้อมูลแบบ Top-N records เพียง 3 วิธีการดังนี้
1. Inline View and ROWNUM
2. WITH Clause and ROWNUM
3. ROW_NUMBER
สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ
โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุด 5 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้
เริ่มต้น Top-N query ตามลำดับเพื่อแก้โจทย์กันค่ะ
Inline View and ROWNUM
Classic Top-N style query
SELECT a.*,rownum
FROM (SELECT *
FROM test_new_student
ORDER BY eng_score desc) a
WHERE ROWNUM <= 5;
ผลลัพธ์:
• จากผลลัพธ์ที่ได้ข้อมูลจะถูกจัดเรียงจากคะแนนจากมากไปน้อยก่อนด้วย ORDER BY clause และหลังจากนั้นก็จะจำกัดจำนวนข้อมูลที่ต้องการด้วย ROWNUM
• Pseudocolumn ROWNUM เป็นค่าตัวเลขแสดงลำดับที่ของการดึงข้อมูลจากตาราง
• กรณีที่ต้องการข้อมูลคะแนนภาษาอังกฤษต่ำสุด ใส่ ASC แทน DESC ตรง ORDER BY clause
WITH Clause and ROWNUM
จากตัวอย่างข้างต้นเรายังสามารถเขียน query ด้วย WITH clause แทนที่ inline view ได้ดังนี้
WITH ordered_query AS
(SELECT *
FROM test_new_student
ORDER BY eng_score desc)
SELECT ordered_query.*,rownum
FROM ordered_query
WHERE rownum <= 5;
ROW_NUMBER
ฟังก์ชัน ROW_NUMBER เป็นฟังก์ชันที่กำหนดค่าลำดับของข้อมูลที่จัดเรียงตามข้อมูลที่กำหนดไว้ใน order_by_clause โดยจะมีค่าเริ่มต้นเท่ากับ 1 โดยเราจะมีวิธีการเขียน query ได้ดังนี้
SELECT *
FROM (SELECT a.*, row_number() OVER (ORDER BY eng_score DESC) AS val_row_number
FROM test_new_student a)
WHERE val_row_number <= 5;
ผลลัพธ์:
สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษแล้ว แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ