[SQL] Finding the Nth Highest Values (JOIN/ORDER BY /LIMIT/DENSE_RANK () OVER)

<1>

-- 1. join / 2. select

SELECT worker_title, salary
FROM worker JOIN title ON worker.worker_id=title.worker_ref_id
WHERE salary >= (SELECT MAX(salary) FROM worker)

 

 

<2>

-- using LIMIT

SELECT *
FROM worker
WHERE salary >= (
    SELECT DISTINCT salary
    FROM worker
    ORDER BY salary DESC
    LIMIT 4, 1);


-- using DENSE_RANK

SELECT first_name, salary, salary_rank
FROM (SELECT first_name,salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
        FROM worker) worker_order
WHERE salary_rank <= 5;

 

 

* LIMIT

1. LIMIT n: Retrieves the first n rows.
2. LIMIT offset, n: Skips the first offset rows and retrieves the next n rows.
3. LIMIT cannot handle duplicate values effectively on its own, so it is often used in subqueries for better control.