I want to fetch the name of person having 3rd highest salary. Is there any way to display any custom message for departments having less than 2 employees?

Eg: In lag() we can use something like LAG(salary, 1, 'first in list').

Currently it shows null

table data

CodePudding user response：

Since I refuse to open your link, I don't know if the table name and column names in my query are exactly as you need. If not, just change them.

SELECT department, name
FROM 
(SELECT department, name, salary, ROW_NUMBER() OVER 
  (PARTITION BY department ORDER BY salary DESC) AS rn
   FROM yourtable
  ) sub 
WHERE rn = 3
UNION ALL
SELECT department, 'No One' 
FROM yourtable
GROUP BY department
HAVING COUNT(department) < 3
ORDER BY department;

There might be shorter options, but this one is quite clear: The first query finds the department and the name of people having the 3rd highest salary in this department. It doesn't select anything for departments with less than three people.

The second query will find all departments having less than three people.

Sidenote: This query will only take one person if multiple persons in a certain department have the same 3rd highest salary. Due to your less detailed description, it's unclear if this is correct for you. It's up to you to adjust this if necessary.