Max Salary per City, Total Salary per Department, and Second-Highest Salary

• MAX per city – SELECT city, MAX(salary) FROM employee GROUP BY city returns the highest salary in each city group.
• SUM per department – SELECT department, SUM(salary) FROM employee GROUP BY department returns the payroll cost for each department.
• Second-highest salary – use DENSE_RANK() OVER (ORDER BY salary DESC) filtered to rank 2, which correctly handles ties by not skipping rank numbers.
• Subquery alternative – SELECT MAX(salary) FROM employee WHERE salary < (SELECT MAX(salary) FROM employee) finds the second-highest distinct value without window functions.
• Name retrieval – to get the employee name alongside MAX(salary) per city, use ROW_NUMBER() OVER (PARTITION BY city ORDER BY salary DESC) and keep row 1.

What does "max salary per city" mean?

This is a grouped aggregate query. You partition the employee rows by city and apply MAX(salary) to each group. The result contains one row per city with the highest salary found in that city.

-- Max salary per city at National Bank of India
SELECT city, MAX(salary) AS max_salary
FROM employee
GROUP BY city
ORDER BY max_salary DESC;

city       | max_salary
-----------+-----------
Mumbai     | 185000.00
Delhi      | 172000.00
Bangalore  | 168000.00

If you also need the name of the person earning that max salary, a plain GROUP BY cannot give it because name is not aggregated. You need a window function approach.

How do you get the employee name alongside the max salary per city?

Use ROW_NUMBER() partitioned by city and ordered by salary descending. Keep only the rows where the row number equals one.

SELECT city, name, salary
FROM (
    SELECT city, name, salary,
           ROW_NUMBER() OVER (
               PARTITION BY city ORDER BY salary DESC, id DESC
           ) AS rn
    FROM employee
) ranked
WHERE rn = 1;

The id DESC tie-breaker ensures deterministic results when two employees in the same city share the top salary.

What does "total salary per department" mean?

This is another grouped aggregate. You partition by department and apply SUM(salary) to compute the payroll cost for each department.

-- Total salary cost per department
SELECT department, SUM(salary) AS total_salary, COUNT(*) AS headcount
FROM employee
GROUP BY department
ORDER BY total_salary DESC;

department   | total_salary | headcount
-------------+--------------+----------
Engineering  | 4250000.00   | 25
Operations   | 3120000.00   | 30
Compliance   | 1850000.00   | 12

Rajesh uses this for the quarterly budget review at National Bank of India.

How do you find the second-highest salary using a window function?

DENSE_RANK() assigns rank 1 to the highest salary and rank 2 to the next distinct salary, even when multiple employees share the top value.

SELECT name, salary
FROM (
    SELECT name, salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employee
) t
WHERE rnk = 2;

name    | salary
--------+-----------
Priya   | 172000.00
Ankit   | 172000.00

Both Priya and Ankit share the second-highest salary, and DENSE_RANK returns them both because it does not skip ranks on ties.

How do you find the second-highest salary without window functions?

Use a subquery that finds the maximum salary strictly below the overall maximum.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

This returns just the value, not the employee name. It handles duplicates of the top salary correctly and returns NULL if there is no second distinct salary.

Why is LIMIT 1 OFFSET 1 wrong for second-highest salary?

A common mistake is to use ORDER BY salary DESC LIMIT 1 OFFSET 1. This fails when the top salary is held by more than one employee, because the second row is just another person at the same top salary.

-- Wrong approach when top salary is duplicated
SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- If top two rows are both 185000, this returns 185000 again

-- Correct approach: use DISTINCT first
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1;

The DISTINCT version works but DENSE_RANK is clearer and also gives you the employee names.

What is the difference between DENSE_RANK and RANK for this problem?

| Feature | RANK() | DENSE_RANK() |
|---|---|---|
| Ties | Assigns same rank, then skips (1,1,3) | Assigns same rank, no skip (1,1,2) |
| Second-highest | WHERE rnk = 2 returns nothing if two share rank 1 | WHERE rnk = 2 always returns the next distinct salary |
| Use case | "Who finished third in a race" (position matters) | "What is the second-highest distinct value" |

For "second-highest salary" always use DENSE_RANK because it does not skip rank numbers after ties.

How do you find the second-highest salary per department?

Add PARTITION BY department to the window function so ranking restarts for each department.

SELECT department, name, salary
FROM (
    SELECT department, name, salary,
           DENSE_RANK() OVER (
               PARTITION BY department ORDER BY salary DESC
           ) AS rnk
    FROM employee
) t
WHERE rnk = 2;

This returns the second-highest earner in each department, which Pankaj uses for internal pay-band analysis at National Bank of India.

How do you implement this in Java with JDBC?

Rajesh writes a utility to pull the max salary per city from the bank's employee database.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class SalaryReportService {

    public void printMaxSalaryPerCity(Connection conn) throws SQLException {
        String sql = "SELECT city, MAX(salary) AS max_salary "
                   + "FROM employee GROUP BY city ORDER BY max_salary DESC";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String city = rs.getString("city");
                BigDecimal maxSalary = rs.getBigDecimal("max_salary");
                System.out.println(city + ": " + maxSalary);
            }
        }
    }

    public BigDecimal getSecondHighestSalary(Connection conn) throws SQLException {
        String sql = "SELECT MAX(salary) AS second_highest "
                   + "FROM employee "
                   + "WHERE salary < (SELECT MAX(salary) FROM employee)";
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return rs.getBigDecimal("second_highest");
            }
            return null;
        }
    }
}

What are the performance considerations?

For MAX and SUM aggregates, an index on (city, salary) or (department, salary) helps the engine avoid a full table scan when the table is large. For the DENSE_RANK query, an index on salary DESC speeds up the sort. For per-department ranking, a composite index on (department, salary DESC) is optimal.

-- Indexes for National Bank of India employee reports
CREATE INDEX idx_emp_city_salary ON employee (city, salary DESC);
CREATE INDEX idx_emp_dept_salary ON employee (department, salary DESC);

Money columns must always be DECIMAL or NUMERIC, never FLOAT or DOUBLE, to avoid rounding errors in financial calculations.

What are common interview follow-up questions?

• How do you find the Nth highest salary (change WHERE rnk = 2 to WHERE rnk = N).
• How do you find the top 3 earners per department (change WHERE rnk = 2 to WHERE rnk <= 3).
• How do you handle NULL salaries (NULL values are ignored by MAX and SUM but could affect DENSE_RANK ordering).
• How do you get the median salary (use PERCENTILE_CONT(0.5) or a ROW_NUMBER approach).