ROW_NUMBER, RANK और DENSE_RANK

ROW_NUMBER, RANK और DENSE_RANK

Verified Tie Dataset

advanced_scores table deliberately Meera और Sana दोनों को X-A में 92 marks देती है। यही tie three functions का real difference दिखाती है।

Side-by-Side Ranking Query

SELECT student_name, class_name, marks,
       ROW_NUMBER() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC, student_id
       ) AS row_no,
       RANK() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC
       ) AS rank_no,
       DENSE_RANK() OVER (
         PARTITION BY class_name
         ORDER BY marks DESC
       ) AS dense_rank_no
FROM advanced_scores
ORDER BY class_name, row_no;

ROW_NUMBER deterministic tie-breaker के लिए student_id जोड़ता है। RANK/DENSE_RANK केवल marks से order करते हैं ताकि equal marks peers रहें।

Verified Ranking Output

X-A के two tied leaders के बाद RANK position 2 skip करता; DENSE_RANK Aarav को 2 देता। ROW_NUMBER distinct row positions 1, 2, 3 देता है।

Deterministic Ordering और Tie Policy

Ranking report स्पष्ट करे equal marks same position share करेंगे या fixed-row selection ties कैसे break करेगी।

• All tied winners: RANK या DENSE_RANK <= N filter करें।
• Exactly N rows: documented tie-breaker के साथ ROW_NUMBER <= N।
• RANK में unique key न जोड़ें जब तक peer ties intentionally destroy न करनी हों।
• Final display order के लिए top-level ORDER BY फिर भी चाहिए।

हर Class के Top Two Rows

WITH ranked AS (
  SELECT student_id, student_name,
         class_name, marks,
         ROW_NUMBER() OVER (
           PARTITION BY class_name
           ORDER BY marks DESC, student_id
         ) AS row_no
  FROM advanced_scores
)
SELECT student_name, class_name, marks, row_no
FROM ranked
WHERE row_no <= 2
ORDER BY class_name, row_no;

Window value same query level WHERE में filter नहीं होती, इसलिए CTE पहले calculate करती है। Nth position के all ties include करने हों तो ROW_NUMBER को RANK से replace करें, भले N से अधिक rows आएँ।

Deduplication और Pagination Patterns

हर student का latest record रखें

WITH latest AS (
  SELECT log_id, student_id, event_time,
         ROW_NUMBER() OVER (
           PARTITION BY student_id
           ORDER BY event_time DESC, log_id DESC
         ) AS row_no
  FROM attendance_log
)
SELECT log_id, student_id, event_time
FROM latest
WHERE row_no = 1;

Unique log_id tie-breaker “latest” deterministic बनाता है। Older duplicates delete करने से पहले review करें। Pagination में ROW_NUMBER stable ordered snapshot label कर सकता है, पर live data changes pages shift कर सकते हैं; frequently changing apps में keyset pagination safer हो सकती है।

Performance, Mistakes और अभ्यास

• Partition/order sorting माँग सकते हैं; EXPLAIN देखें।
• Ranking stage में केवल required columns project करें।
• Indexing filtering/access मदद कर सकती, हर window sort eliminate नहीं।
• Reproducibility में deterministic ordering के बिना ROW_NUMBER न लें।
• First, Nth और last positions पर ties test करें।

अभ्यास: RANK से all class winners; DENSE_RANK से top two score bands; ROW_NUMBER से exactly two students; X-B second place tie जोड़कर result counts compare करें।

Official संदर्भ

• MySQL 8.4: Window Function Descriptions
• MySQL 8.4: Window Concepts and Syntax
• MySQL 8.4: Window Function Optimization

Syntax और behavior official MySQL 8.4 manual से जाँचे गए हैं। Production से पहले अपने server पर plans और limits verify करें।