Sample sql for giving custom rownumbering depending on some conditions

Custom Row Numbering in SQL Using ROW_NUMBER()

In SQL, the ROW_NUMBER() function is extremely useful when you want to assign a unique sequential number to rows within a result set. This is especially helpful when partitioning data or performing advanced reporting operations.

📌 When to Use ROW_NUMBER()

• To assign a unique row number per group or partition
• To filter the first or last row in each group
• To remove duplicates while retaining the latest or earliest record

💻 SQL Syntax Example

SELECT ename,
       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS enum
FROM emp;

🔍 Explanation

• ENAME – Employee name column from the emp table.
• ROW_NUMBER() – Assigns a unique row number starting at 1 for each partition.
• PARTITION BY column1 – Resets the row numbering for each group defined by column1.
• ORDER BY column2 – Determines the order in which row numbers are assigned within each group.
• enum – An alias for the generated row number column.

📈 Common Use Cases

1. Getting the top-ranked employee per department
2. Removing duplicates while keeping the most recent entry
3. Paginating results in a web application

💡 Pro Tip

You can replace ROW_NUMBER() with RANK() or DENSE_RANK() depending on whether you want to allow duplicate ranks.

Using ROW_NUMBER() is a clean and powerful way to handle advanced SQL queries where row-based logic is required. Mastering it can significantly simplify your analytical tasks!