{"id":66919,"date":"2022-09-02T09:01:43","date_gmt":"2022-09-02T09:01:43","guid":{"rendered":"https:\/\/www.cryptocabaret.com\/?p=66919"},"modified":"2022-09-02T09:01:43","modified_gmt":"2022-09-02T09:01:43","slug":"how-to-display-the-presence-and-absence-of-nth-highest-group-wise-values-in-sql","status":"publish","type":"post","link":"https:\/\/www.cryptocabaret.com\/?p=66919","title":{"rendered":"How to display the presence and absence of nth-highest group-wise values in SQL"},"content":{"rendered":"

How to display the presence and absence of nth-highest group-wise values in SQL<\/span>
\nMohammed Kamil Khan<\/a><\/span>
\nFri, 09\/02\/2022 – 03:00<\/span><\/p>\n

\n
Register<\/a> or Login<\/a> to like<\/div>\n
Register<\/a> or Login<\/a> to like<\/div>\n<\/div>\n
\n

While skimming through SQL to prepare for interviews, I often come across this question: Find the employee with the highest or (second-highest) salary by joining a table containing employee information with another that contains department information. This raises a further question: What about finding the employee who earns the nth-<\/i>highest salary department-wide?<\/p>\n<\/p>\n

\n
More great content<\/div>\n
\n
Free online course: RHEL technical overview<\/a><\/div>\n
Learn advanced Linux commands<\/a><\/div>\n
Download cheat sheets<\/a><\/div>\n
Find an open source alternative<\/a><\/div>\n
Explore open source resources<\/a><\/div>\n<\/p><\/div>\n<\/p><\/div>\n

Now I want to pose a more complex scenario: What will happen when a department doesn’t have an employee earning the nth-highest salary? For example, a department with only two employees will not have an employee earning the third-highest salary.<\/p>\n

Here’s my approach to this question:<\/p>\n

Create department and employee tables<\/h2>\n

I create a table that includes fields such as dept_id<\/code> and dept_name<\/code>.<\/p>\n

\n
CREATE<\/span> TABLE<\/span> department (<\/span>
\n\u00a0 \u00a0 dept_id INT<\/span>,<\/span>
\n\u00a0 \u00a0 dept_name VARCHAR<\/span>(<\/span>60<\/span>)<\/span>
)<\/span>;<\/div><\/div><\/pre>\n
Now I insert various departments into the new table.<\/p>\n
\n
INSERT<\/span> INTO<\/span> department (<\/span>dept_id,<\/span>dept_name)<\/span>
VALUES<\/span> (<\/span>780<\/span>,<\/span>'HR'<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> department (<\/span>dept_id,<\/span>dept_name)<\/span>
VALUES<\/span> (<\/span>781<\/span>,<\/span>'Marketing'<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> department (<\/span>dept_id,<\/span>dept_name)<\/span>
VALUES<\/span> (<\/span>782<\/span>,<\/span>'Sales'<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> department (<\/span>dept_id,<\/span>dept_name)<\/span>
VALUES<\/span> (<\/span>783<\/span>,<\/span>'Web Dev'<\/span>)<\/span>;<\/div><\/div><\/pre>\n
\n
  \"A<\/div>\n
Image by: <\/span><\/p>\n
Figure 1. The department table (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
Next, I create another table incorporating the fields first_name<\/code>, last_name<\/code>, dept_id<\/code>, and salary<\/code>.<\/p>\n
\n
CREATE<\/span> TABLE<\/span> employee (<\/span>
\n\u00a0 \u00a0 first_name VARCHAR<\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n\u00a0 \u00a0 last_name VARCHAR<\/span>(<\/span>100<\/span>)<\/span>,<\/span>
\n\u00a0 \u00a0 dept_id INT<\/span>,<\/span>
\n\u00a0 \u00a0 salary INT<\/span>
)<\/span>;<\/div><\/div><\/pre>\n
Then I insert values into the table:<\/p>\n
\n
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Sam'<\/span>,<\/span>'Burton'<\/span>,<\/span>781<\/span>,<\/span>80000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Peter'<\/span>,<\/span>'Mellark'<\/span>,<\/span>780<\/span>,<\/span>90000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Happy'<\/span>,<\/span>'Hogan'<\/span>,<\/span>782<\/span>,<\/span>110000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Steve'<\/span>,<\/span>'Palmer'<\/span>,<\/span>782<\/span>,<\/span>120000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Christopher'<\/span>,<\/span>'Walker'<\/span>,<\/span>783<\/span>,<\/span>140000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Richard'<\/span>,<\/span>'Freeman'<\/span>,<\/span>781<\/span>,<\/span>85000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Alex'<\/span>,<\/span>'Wilson'<\/span>,<\/span>782<\/span>,<\/span>115000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Harry'<\/span>,<\/span>'Simmons'<\/span>,<\/span>781<\/span>,<\/span>90000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Thomas'<\/span>,<\/span>'Henderson'<\/span>,<\/span>780<\/span>,<\/span>95000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Ronald'<\/span>,<\/span>'Thompson'<\/span>,<\/span>783<\/span>,<\/span>130000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'James'<\/span>,<\/span>'Martin'<\/span>,<\/span>783<\/span>,<\/span>135000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Laurent'<\/span>,<\/span>'Fisher'<\/span>,<\/span>780<\/span>,<\/span>100000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Tom'<\/span>,<\/span>'Brooks'<\/span>,<\/span>780<\/span>,<\/span>85000<\/span>)<\/span>;
INSERT<\/span> INTO<\/span> employee (<\/span>first_name,<\/span>last_name,<\/span>dept_id,<\/span>salary)<\/span>
VALUES<\/span> (<\/span>'Tom'<\/span>,<\/span>'Bennington'<\/span>,<\/span>783<\/span>,<\/span>140000<\/span>)<\/span>;<\/div><\/div><\/pre>\n
\n
  \"A<\/div>\n
Image by: <\/span><\/p>\n
Figure 2. A table of employees ordered by department ID (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
I can infer the number of employees in each department using this table (department ID:number of employees):<\/p>\n
    \n
  • 780:4<\/li>\n
  • 781:3<\/li>\n
  • 782:3<\/li>\n
  • 783:4<\/li>\n<\/ul>\n
    If I want the view the second-highest-earning employees from different departments, along with their department’s name (using DENSE_RANK<\/code>), the table will be as follows:<\/p>\n
    \n
      \"A<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 3. The second-highest-earning employee in each department (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    If I apply the same query to find the fourth-highest-earning employees, the output will be only Tom Brooks of department 780 (HR), with a salary of $85,000.<\/p>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 4. The fourth-highest-earning employee (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Though department 783 (Web Dev) has four employees, two (James Martin and Ronald Thompson) will be classified as the third-highest-earning employees of that department, since the top two earners have the same salary.<\/p>\n
    Finding the nth highest<\/h2>\n
    Now, to the main question: What if I want to display the dept_ID<\/code> and dept_name<\/code> with null values for employee-related fields for departments that do not have an nth-highest-earning employee?<\/p>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 5. All departments listed, whether or not they have an nth-highest-earning employee (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    The table displayed in Figure 5 <\/b>is what I am aiming to obtain when specific departments do not have an nth-highest-earning employee: The marketing, sales, and web dev departments are listed, but the name and salary fields contain a null value.<\/p>\n
    The ultimate query that helps obtain the table in Figure 5 is as follows:<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>WITH<\/span> null1 AS<\/span> (<\/span>SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \nemployee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A)<\/span>,<\/span>
    \nfull1 AS<\/span> (<\/span>SELECT<\/span> dept_id,<\/span> dept_name FROM<\/span> department WHERE<\/span> dept_id NOT<\/span> IN<\/span> (<\/span>SELECT<\/span> dept_id FROM<\/span> null1 WHERE<\/span> dept_id IS<\/span> NOT<\/span> NULL<\/span>)<\/span>)<\/span>,<\/span>
    \nnulled AS<\/span>(<\/span>SELECT<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_id IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_id ELSE<\/span> null1.<\/span>dept_id END<\/span>,<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_name IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_name ELSE<\/span> null1.<\/span>dept_name END<\/span>,<\/span>
    \nfirst_name,<\/span>last_name,<\/span>salary
    FROM<\/span> null1 RIGHT<\/span> JOIN<\/span> full1 ON<\/span> null1.<\/span>dept_id=<\/span>full1.<\/span>dept_id)<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> null1
    UNION<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> nulled
    ORDER<\/span> BY<\/span> dept_id)<\/span>
    \nB;<\/div><\/div><\/pre>\n
    Breakdown of the query<\/h2>\n
    I will break down the query to make it less overwhelming.<\/p>\n
    Use DENSE_RANK()<\/code> to display employee and department information (not involving null for the absence of the nth-highest-earning member):<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    \n\u00a0 SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \n\u00a0 \u00a0employee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    \n\u00a0 \u00a0FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    \n\u00a0 \u00a0ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    \n\u00a0 \u00a0WHERE<\/span> rank1=<\/span>4<\/span><\/div><\/div><\/pre>\n
    Output:<\/p>\n
    \n
      \"A<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 6. The fourth-highest earner (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Exclude the rank1<\/code> column from the table in Figure 6, which identifies only one employee with a fourth-highest salary, even though there are four employees in another department.<\/p>\n
    \n
    SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    \n\u00a0 \u00a0 FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    \n\u00a0 SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \n\u00a0 \u00a0employee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    \n\u00a0 \u00a0FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    \n\u00a0 \u00a0ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    \n\u00a0 \u00a0WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A<\/div><\/div><\/pre>\n
    Output:<\/p>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 7. The fourth-highest earner table without the rank 1 column (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Point out the departments from the department table that do not have an nth-highest-earning employee:<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>WITH<\/span> null1 AS<\/span> (<\/span>SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    \n\u00a0 \u00a0 FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    \n\u00a0 SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \n\u00a0 \u00a0employee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    \n\u00a0 \u00a0FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    \n\u00a0 \u00a0ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    \n\u00a0 \u00a0WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A)<\/span>,<\/span>
    \nfull1 AS<\/span> (<\/span>SELECT<\/span> dept_id,<\/span> dept_name FROM<\/span> department WHERE<\/span> dept_id NOT<\/span> IN<\/span> (<\/span>SELECT<\/span> dept_id FROM<\/span> null1 WHERE<\/span> dept_id IS<\/span> NOT<\/span> NULL<\/span>)<\/span>)<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> full1)<\/span>B<\/div><\/div><\/pre>\n
    Output:<\/p>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 8. The full1 table listing the departments without a fourth-highest earner (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Replace full1<\/code> in the last line of the above code with null1<\/code>:<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>WITH<\/span> null1 AS<\/span> (<\/span>SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    \n\u00a0 \u00a0 FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    \n\u00a0 SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \n\u00a0 \u00a0employee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    \n\u00a0 \u00a0FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    \n\u00a0 \u00a0ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    \n\u00a0 \u00a0WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A)<\/span>,<\/span>
    \nfull1 AS<\/span> (<\/span>SELECT<\/span> dept_id,<\/span> dept_name FROM<\/span> department WHERE<\/span> dept_id NOT<\/span> IN<\/span> (<\/span>SELECT<\/span> dept_id FROM<\/span> null1 WHERE<\/span> dept_id IS<\/span> NOT<\/span> NULL<\/span>)<\/span>)<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> null1)<\/span>B<\/div><\/div><\/pre>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 9. The null1 table listing all departments, with null values for those without a fourth-highest earner (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Now, I fill the null values of the\u00a0dept_id<\/code> and dept_name<\/code> fields in Figure 9 with the corresponding values from Figure 8.<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>WITH<\/span> null1 AS<\/span> (<\/span>SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    \n\u00a0 \u00a0 FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    \n\u00a0 SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \n\u00a0 \u00a0employee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    \n\u00a0 \u00a0FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    \n\u00a0 \u00a0ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    \n\u00a0 \u00a0WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A)<\/span>,<\/span>
    \nfull1 AS<\/span> (<\/span>SELECT<\/span> dept_id,<\/span> dept_name FROM<\/span> department WHERE<\/span> dept_id NOT<\/span> IN<\/span> (<\/span>SELECT<\/span> dept_id FROM<\/span> null1 WHERE<\/span> dept_id IS<\/span> NOT<\/span> NULL<\/span>)<\/span>)<\/span>,<\/span>
    \nnulled AS<\/span>(<\/span>SELECT<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_id IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_id ELSE<\/span> null1.<\/span>dept_id END<\/span>,<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_name IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_name ELSE<\/span> null1.<\/span>dept_name END<\/span>,<\/span>
    \nfirst_name,<\/span>last_name,<\/span>salary
    FROM<\/span> null1 RIGHT<\/span> JOIN<\/span> full1 ON<\/span> null1.<\/span>dept_id=<\/span>full1.<\/span>dept_id)<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> nulled)<\/span> B;<\/div><\/div><\/pre>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 10. The result of the nulled query (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    The nulled query uses CASE WHEN<\/code> on the nulls encountered in the dept_id<\/code> and dept_name<\/code> columns of the null1<\/code> table and replaces them with the corresponding values in the full1<\/code> table. Now all I need to do is apply UNION<\/code> to the tables obtained in Figure 7 and Figure 10. This can be accomplished by declaring the last query in the previous code using WITH<\/code> and then UNION<\/code>-izing it with null1<\/code>.<\/p>\n
    \n
    SELECT<\/span> *<\/span> FROM<\/span> (<\/span>WITH<\/span> null1 AS<\/span> (<\/span>SELECT<\/span> A.<\/span>dept_id,<\/span> A.<\/span>dept_name,<\/span> A.<\/span>first_name,<\/span> A.<\/span>last_name,<\/span> A.<\/span>salary
    FROM<\/span> (<\/span>SELECT<\/span> *<\/span> FROM<\/span> (<\/span>
    SELECT<\/span> department.<\/span>dept_id,<\/span> department.<\/span>dept_name,<\/span> employee.<\/span>first_name,<\/span> employee.<\/span>last_name,<\/span>
    \nemployee.<\/span>salary,<\/span> DENSE_RANK(<\/span>)<\/span> OVER<\/span> (<\/span>PARTITION BY<\/span> employee.<\/span>dept_id ORDER<\/span> BY<\/span> employee.<\/span>salary DESC<\/span>)<\/span> AS<\/span> Rank1
    FROM<\/span> employee INNER<\/span> JOIN<\/span> department
    ON<\/span> employee.<\/span>dept_id=<\/span>department.<\/span>dept_id)<\/span> AS<\/span> k
    WHERE<\/span> rank1=<\/span>4<\/span>)<\/span>A)<\/span>,<\/span>
    \nfull1 AS<\/span> (<\/span>SELECT<\/span> dept_id,<\/span> dept_name FROM<\/span> department WHERE<\/span> dept_id NOT<\/span> IN<\/span> (<\/span>SELECT<\/span> dept_id FROM<\/span> null1 WHERE<\/span> dept_id IS<\/span> NOT<\/span> NULL<\/span>)<\/span>)<\/span>,<\/span>
    \nnulled AS<\/span>(<\/span>SELECT<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_id IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_id ELSE<\/span> null1.<\/span>dept_id END<\/span>,<\/span>
    CASE<\/span> WHEN<\/span> null1.<\/span>dept_name IS<\/span> NULL<\/span> THEN<\/span> full1.<\/span>dept_name ELSE<\/span> null1.<\/span>dept_name END<\/span>,<\/span>
    \nfirst_name,<\/span>last_name,<\/span>salary
    FROM<\/span> null1 RIGHT<\/span> JOIN<\/span> full1 ON<\/span> null1.<\/span>dept_id=<\/span>full1.<\/span>dept_id)<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> null1
    UNION<\/span>
    SELECT<\/span> *<\/span> FROM<\/span> nulled
    ORDER<\/span> BY<\/span> dept_id)<\/span>
    \nB;<\/div><\/div><\/pre>\n
    \n
      \"The<\/div>\n
    Image by: <\/span><\/p>\n
    Figure 11. The final result (Mohammed Kamil Khan, CC BY-SA 4.0)<\/p>\n<\/div>\n<\/article>\n
    Now I can infer from Figure 11 that marketing, sales, and web dev are the departments that do not have any employees earning the fourth-highest salary.<\/p>\n<\/div>\n
    \n
    A step-by-step breakdown of the query.<\/p>\n<\/div>\n
    \n
    \n
      \"Digital<\/div>\n<\/article>\n<\/div>\n
    \n
    Databases<\/a><\/div>\n<\/p><\/div>\n
    What to read next<\/div>\n
    \n
    A hands-on tutorial of SQLite3<\/a><\/div>\n
    Improve your database knowledge with this MariaDB and MySQL cheat sheet<\/a><\/div>\n<\/p><\/div>\n

    \n        \"Creative<\/a>This work is licensed under a Creative Commons Attribution-Share Alike 4.0 International License.<\/div>\n
    \n
    \n
    Register<\/a> or Login<\/a> to post a comment.<\/div>\n<\/p><\/div>\n<\/section>\n
    Powered by WPeMatico<\/a><\/small><\/p>\n","protected":false},"excerpt":{"rendered":"
    How to display the presence and absence of nth-highest group-wise values in SQL Mohammed Kamil Khan Fri, 09\/02\/2022 – 03:00 Register or Login to like Register or Login to like While skimming through SQL to prepare for interviews, I often come across this question: Find the employee with the highest or (second-highest) salary by joining […]<\/p>\n","protected":false},"author":1,"featured_media":66920,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[307],"tags":[],"class_list":["post-66919","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-open-source"],"_links":{"self":[{"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/posts\/66919","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=66919"}],"version-history":[{"count":0,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/posts\/66919\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=\/wp\/v2\/media\/66920"}],"wp:attachment":[{"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=66919"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=66919"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cryptocabaret.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=66919"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}