SQL Important Queries

1/1/1970

SQL Important Queries

Largest Value

1. Using Subqueries

Largest

SELECT MAX(column_name) AS largest_value 
FROM table_name;

Second Largest

SELECT MAX(salary) AS second_largest_salary 
FROM employees 
WHERE salary < (
	SELECT MAX(salary) 
	FROM employees);

Nth Largest

SELECT MAX(column_name) AS nth_largest
FROM table_name
WHERE column_name < (
    SELECT MAX(column_name)
    FROM table_name
    WHERE column_name < (
        SELECT MAX(column_name)
        FROM table_name
        -- Repeat this pattern N-1 times
    )
);
Using LIMIT and OFFSET
  1. Sort the values in descending order (ORDER BY column_name DESC).
  2. Skip the first (N-1) values (OFFSET N-1) and fetch one row (LIMIT 1).
SELECT column_name
FROM table_name
ORDER BY column_name DESC
LIMIT 1 OFFSET N-1;
  1. For MySQL & PostgreSQL:
SELECT * FROM table_name
LIMIT 5;
  1. For SQL Server:
SELECT TOP 5 * 
FROM table_name;
  1. For Oracle:
SELECT * 
FROM table_name
WHERE ROWNUM <= 5;