0Pricing
SQL Interview Prep · บทเรียน

ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้

เหตุใด ORDER BY ร่วมกับ LIMIT จึงอาจให้ผลไม่แน่นอนหากไม่มีตัวตัดสินกรณีค่าเท่ากัน

ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้ เป็นบทเรียน SQL Interview Prep ฟรีบน CoddyKit นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน คุณสามารถอ่านบทเรียนทั้งหมดด้านล่างฟรี — จากนั้นลองปฏิบัติด้วยตัวคุณเองในเบราว์เซอร์พร้อมตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7 บทเรียนนี้เป็นส่วนหนึ่งของเส้นทางการเรียน SQL Interview Prep และความก้าวหน้าของคุณจะซิงค์ข้ามเว็บและแอป CoddyKit คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

บั๊กลับในคิวรี N อันดับสูงสุด

คำขอว่า "ขอพนักงานที่ได้รับเงินเดือนสูงสุด 5 คน" ดูเหมือนง่าย: ORDER BY salary DESC LIMIT 5 แต่ผู้สัมภาษณ์อาจวางกับดักไว้ จะเกิดอะไรขึ้นหากมีหกคนได้รับเงินเดือนเท่ากันตรงขอบเขต หรือมีหลายแถวที่มีค่าเท่ากัน

ประเด็นสำคัญคือ ความแน่นอนของผลลัพธ์ เมื่อคีย์ที่ใช้เรียงลำดับมีค่าซ้ำ LIMIT จะตัดข้อมูลโดยไม่มีลำดับที่แน่นอน และแถวที่คืนมาอาจเปลี่ยนไปในแต่ละครั้งที่เรียกใช้ บทเรียนนี้จะช่วยให้คิวรี N อันดับสูงสุดให้ผลลัพธ์ที่เชื่อถือได้

เหตุใด ORDER BY + LIMIT จึงอาจให้ผลลัพธ์ไม่แน่นอน

ลองพิจารณาเงินเดือนที่อันดับ 4, 5 และ 6 เท่ากับ 50000 ทั้งหมด ORDER BY salary DESC LIMIT 5 ต้องคืนผลลัพธ์ให้ได้ exactamente 5 แถว จึงเก็บไว้สองแถวจากสามแถวที่มีค่าเท่ากันและทิ้งอีกหนึ่งแถว แต่ไม่มีกฎกำหนดว่าสองแถวนั้นต้องเป็นแถวใด

เมื่อเรียกใช้คิวรีสองครั้ง หรือหลังจากตัวปรับประสิทธิภาพเปลี่ยนแผนการทำงาน คุณอาจได้คนละคนกัน ความไม่แน่นอนนี้คือบั๊กที่ผู้สัมภาษณ์ต้องการให้คุณมองเห็น

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

วิธีแก้ที่ 1: เพิ่มตัวตัดสินกรณีเสมอที่ไม่ซ้ำ

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

วิธีนี้ไม่ได้เปลี่ยนว่าเงินเดือนใดจะปรากฏ แต่ทำให้การเลือกแถวในกลุ่มที่มีค่าเท่ากันคงที่ในการเรียกใช้แต่ละครั้ง

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC
LIMIT 5;

วิธีแก้ที่ 2: รวมแถวที่มีค่าเท่ากันทั้งหมดด้วย WITH TIES

บางครั้งข้อกำหนดคือ "รวมทุกคนที่มีค่าเท่ากับค่าขอบเขต" ไม่ใช่การคืนผลลัพธ์ให้ได้ N แถวพอดี SQL มาตรฐานและ SQL Server รองรับ WITH TIES ซึ่งจะคืนแถวเพิ่มเติมที่มีค่า ORDER BY ตรงกับแถวสุดท้าย

หากเงินเดือนอันดับที่ 5 มีสามคนได้รับเท่ากัน วิธีนี้จะคืนทั้งหมด 7 แถว โปรดสังเกตว่า WITH TIES ต้องใช้ร่วมกับ ORDER BY

SELECT name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS WITH TIES;

ทำความเข้าใจข้อกำหนดให้ชัดเจนก่อน

ก่อนเขียนโค้ด ให้ถามผู้สัมภาษณ์ว่า "หากมีค่าซ้ำกันตรงขอบเขต คุณต้องการให้คืนพอดี N แถว หรือคืนทุกแถวที่มีค่าเท่ากัน" คำถามเพื่อขอความชัดเจนเพียงคำถามเดียวนี้แสดงให้เห็นถึงประสบการณ์ระดับอาวุโส

  • ต้องการพอดี N แถวและให้ผลคงที่: เพิ่มตัวตัดสินกรณีเสมอที่ไม่ซ้ำ
  • ต้องการรวมแถวที่มีค่าเท่ากันทั้งหมด: ใช้ WITH TIES หรือ RANK
  • ต้องการค่าที่ไม่ซ้ำกัน: ใช้ DENSE_RANK

แนวทางฟังก์ชันหน้าต่างที่ใช้ได้ข้ามระบบ

ระบบฐานข้อมูลจำนวนมากไม่มี WITH TIES รูปแบบที่ใช้ได้ทั่วไปและมีประสิทธิภาพคือใช้ฟังก์ชันหน้าต่างสำหรับจัดอันดับในคิวรีย่อยหรือ CTE แล้วจึงกรองตามอันดับ ROW_NUMBER จะคืนพอดี N แถวโดยใช้คีย์การเรียงลำดับที่กำหนดผลลัพธ์แน่นอน

คุณต้องห่อฟังก์ชันหน้าต่างไว้ภายในคิวรีย่อย เพราะไม่สามารถอ้างอิงฟังก์ชันดังกล่าวโดยตรงใน WHERE ได้

SELECT name, salary
FROM (
  SELECT name, salary,
         ROW_NUMBER() OVER (ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 5;

ใช้ RANK เพื่อคงแถวที่มีค่าเท่ากัน

เปลี่ยนจาก ROW_NUMBER เป็น RANK เมื่อคุณต้องการเก็บแถวที่มีค่าเท่ากันทั้งหมดและยอมให้ลำดับมีช่องว่าง หากมีสามแถวอยู่ในอันดับ 4 เท่ากัน ทั้งหมดจะได้อันดับ 4 และอันดับถัดไปคือ 7

จากนั้นการกรองด้วย rank <= 5 จะคืนทุกแถวในห้าตำแหน่งเงินเดือนสูงสุด โดยรวมแถวที่มีค่าเท่ากันด้วย

SELECT name, salary
FROM (
  SELECT name, salary,
         RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 5;

ใช้ DENSE_RANK สำหรับค่าที่ไม่ซ้ำกัน N อันดับสูงสุด

คำว่า "ระดับเงินเดือน 3 อันดับสูงสุด" (ไม่ใช่บุคคล 3 อันดับสูงสุด) หมายถึงค่าที่ไม่ซ้ำกัน DENSE_RANK จะกำหนดอันดับเดียวกันให้ค่าที่เท่ากันและไม่ข้ามหมายเลข ดังนั้น dense_rnk <= 3 จะคืนทุกคนที่ได้รับหนึ่งในสามเงินเดือนสูงสุดที่มีค่าไม่ซ้ำกัน

การรู้ว่าควรใช้ฟังก์ชันจัดอันดับใดกับข้อความกำหนดความต้องการแต่ละแบบ เป็นสิ่งที่ช่วยแยกผู้สมัครที่มีความเข้าใจดีออกจากผู้สมัครทั่วไป

SELECT name, salary
FROM (
  SELECT name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
) ranked
WHERE drnk <= 3;

กรณีพิเศษของอันดับ 1 สูงสุด

สำหรับแถวเดียวที่มีค่าสูงสุด ORDER BY ... LIMIT 1 ใช้งานได้ แต่ยังมีความเสี่ยงเมื่อมีค่าซ้ำกัน หากต้องการทุกแถวที่มีค่าสูงสุด ให้เปรียบเทียบกับค่าสูงสุดจากคิวรีย่อย หรือใช้ RANK() = 1

รูปแบบที่ใช้คิวรีย่อยหาค่าสูงสุดอ่านเข้าใจง่ายและทำงานได้กับระบบฐานข้อมูลทุกแบบ

SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

เปรียบเทียบแนวทางต่าง ๆ

สรุปว่าเมื่อใดควรใช้เครื่องมือแต่ละแบบเพื่อให้คิวรี N อันดับสูงสุดเชื่อถือได้:

  • LIMIT + ตัวตัดสินกรณีเสมอที่ไม่ซ้ำ: ได้พอดี N แถว ผลลัพธ์คงที่ และเรียบง่ายที่สุด
  • FETCH ... WITH TIES: ได้พอดี N แถวรวมแถวที่มีค่าเท่ากันตรงขอบเขต เป็น SQL มาตรฐาน
  • ROW_NUMBER: ได้พอดี N แถว ให้ผลแน่นอน และใช้ได้กับทุกระบบ
  • RANK: ได้ N ตำแหน่งสูงสุดโดยรวมแถวที่มีค่าเท่ากันทั้งหมด
  • DENSE_RANK: ได้ N ค่าสูงสุดที่ไม่ซ้ำกัน

ตัวอย่าง N อันดับสูงสุดต่อกลุ่ม

แนวทางที่ใช้ฟังก์ชันหน้าต่างสามารถนำไปประยุกต์ใช้ได้อย่างยืดหยุ่น เพียงเพิ่ม PARTITION BY เพื่อหาค่าสูงสุด N อันดับภายในแต่ละกลุ่ม เช่น ผู้มีรายได้สูงสุด 2 อันดับต่อแผนก โดยใช้ตัวกรอง rn <= n แบบเดิมหลังจากแบ่งพาร์ทิชันแล้ว

การหา N อันดับสูงสุดต่อกลุ่มเป็นหนึ่งในโจทย์สัมภาษณ์งานจริงที่พบบ่อยที่สุด และสร้างขึ้นจากรูปแบบเดียวกับที่คุณเพิ่งเรียนรู้

SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (PARTITION BY department
                            ORDER BY salary DESC, id ASC) AS rn
  FROM employees
) ranked
WHERE rn <= 2;

ตรวจสอบอย่างรวดเร็ว

จับคู่ข้อกำหนดกับฟังก์ชันที่ถูกต้อง

ทบทวน

หากต้องการคืน N อันดับสูงสุดอย่างเชื่อถือได้:

  • ORDER BY ... LIMIT เพียงอย่างเดียวให้ผลลัพธ์ไม่แน่นอนเมื่อคีย์ที่ใช้เรียงลำดับมีค่าซ้ำ
  • เพิ่ม ตัวตัดสินกรณีเสมอที่ไม่ซ้ำ เพื่อให้ได้ผลลัพธ์พอดี N แถวที่คงที่
  • ใช้ WITH TIES หรือ RANK เพื่อเก็บแถวที่มีค่าเท่ากันตรงขอบเขต
  • ใช้ DENSE_RANK สำหรับ N ค่าสูงสุดที่ไม่ซ้ำกัน
  • ทำความเข้าใจให้ชัดเจนเสมอว่าผู้สัมภาษณ์ต้องการพอดี N แถวหรือแถวที่มีค่าเท่ากันทั้งหมด

คำถามที่พบบ่อย

บทเรียน “ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้” ฟรีหรือไม่

ใช่ — ข้อความเต็มของ “ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้” ฟรีให้อ่านที่นี่บนเว็บ เพื่อปฏิบัติแบบโต้ตอบ (ตัวแก้ไขโค้ดในตัวและติวเตอร์ AI ตลอด 24/7) และปลดล็อคส่วนที่เหลือของคอร์ส SQL Interview Prep ให้อัปเกรดเป็น CoddyKit PRO คอร์ส SQL Interview Prep มีบทเรียนทั้งหมด 4 บทเรียน

คุณจะเรียนรู้อะไรในบทเรียน “ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้”

เหตุใด ORDER BY ร่วมกับ LIMIT จึงอาจให้ผลไม่แน่นอนหากไม่มีตัวตัดสินกรณีค่าเท่ากัน คุณปฏิบัติ SQL Interview Prep ด้วยโค้ดที่ใช้งานได้จริงที่คุณเรียกใช้โดยตรงในเบราว์เซอร์ และติวเตอร์ AI ตลอด 24/7 ตอบคำถามของคุณขณะที่คุณไปผ่านบทเรียน

คุณต้องมีประสบการณ์ก่อนที่จะเริ่มเรียน SQL Interview Prep หรือไม่

ไม่จำเป็นต้องมีประสบการณ์มาก่อน SQL Interview Prep บน CoddyKit ออกแบบมาสำหรับผู้เริ่มต้นไปจนถึงผู้เรียนขั้นสูง คุณสามารถเริ่มต้นที่นี่หรือเริ่มจากตัวแรกและเรียนด้วยความเร็วของคุณเอง นี่คือบทเรียนที่ 3 จากทั้งหมด 4 บทเรียน

บทเรียน “ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้” ใช้เวลานานแค่ไหน

บทเรียน CoddyKit ส่วนใหญ่ใช้เวลาประมาณ 5–10 นาที แต่ละบทเรียนจึงสั้นและเป็นแบบโต้ตอบ คุณสามารถก้าวหน้าอย่างต่อเนื่องและกลับมาเรียนต่อจากตรงที่เพิ่งหยุดบนเว็บและแอปได้เลย

ฉันเขียนและรันโค้ดในบทเรียน SQL Interview Prep นี้ได้ไหม

ได้ บทเรียน SQL Interview Prep ทุกบทมีตัวแก้ไขโค้ดในตัว คุณจึงเขียนและรันโค้ดจริงได้เลยในเบราว์เซอร์ และได้รับข้อเสนอแนะจาก AI ในทันที — ไม่ต้องติดตั้งในเครื่องของคุณ

บทเรียนทั้งหมดในหลักสูตรนี้

  1. การจัดเรียงหลายคอลัมน์และตำแหน่งของ NULL
  2. LIMIT, OFFSET และ FETCH FIRST
  3. ส่งคืนแถว N อันดับแรกอย่างเชื่อถือได้
  4. จัดเรียงด้วยนิพจน์และนามแฝง
← กลับไปที่ SQL Interview Prep