Window functions: rank without shrinking
Window functions calculate across related rows while keeping individual rows. OVER defines the window. ROW_NUMBER assigns unique row numbers; RANK leaves gaps after ties; DENSE_RANK does not.
हिन्दी में समझें
विंडो फ़ंक्शन व्यक्तिगत पंक्तियाँ रखते हुए संबंधित पंक्तियों पर गणना करता है। OVER विंडो है। ROW_NUMBER अलग क्रम, RANK बराबरी के बाद अंतर और DENSE_RANK बिना अंतर देता है।
अभ्यास: हर विभाग में salary घटता फिर id पर ROW_NUMBER दें; name, department, rn दिखाएँ। अंतिम क्रम department, rn।
Hinglish mein samjhein
Window functions rows ko rakhte hue related rows par calculate karte hain. OVER window define karta hai. ROW_NUMBER unique numbering, RANK ties ke baad gaps aur DENSE_RANK no gaps deta hai.
Practice: Har department mein salary DESC phir id se ROW_NUMBER; name, department, rn; final sort department,rn.
A small SQL example
SELECT name,salary,DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_rank FROM employees ORDER BY salary DESC,id;Examples use the fictional shop database bundled with the SQL playground: customers, products, orders, order_items and employees. The engine is SQLite; other SQL dialects may differ.
Try this example in the SQL playground →Practise this idea
Return name, department and ROW_NUMBER() within each department by salary descending then id; sort final output by department and row number.
Check answer & explanation
SELECT name,department,ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC,id) AS rn FROM employees ORDER BY department,rn;PARTITION BY restarts numbering in each department. Salary descending and id provide a deterministic order within each partition.
Stuck in the course? Choose Check answer for either the challenge or quiz. Read the explanation and continue to the next lesson at your own pace.
Check your understanding
Which keeps rows while calculating across them?
Check the quiz answer
Window functions
Window functions calculate across related rows while keeping individual rows. OVER defines the window. ROW_NUMBER assigns unique row numbers; RANK leaves gaps after ties; DENSE_RANK does not.
References & corrections
Use the official SQLite documentation for full language rules. Read how these lessons are made and checked. Found a mistake? Report it to Excel Keys with this lesson URL and a reproducible example.