Select info from table where row has max date

Make it slick with window functions

SQL window functions are superb tools when working with large datasets:

SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY date_column DESC) as rn
    FROM your_table
) t
WHERE t.rn = 1;


This script employs ROW_NUMBER() to assign unique identification to each row within a partition of rows with the same id. The output is sorted by date_column in descending order. We are after rows bearing 1, pointing to the latest date.

Don't attract syntax errors

Do you want syntax errors? Because that's how you get syntax errors! Stay away from reserved keywords as column names (date or check).

SELECT [date]
FROM your_table


Leave the extras on the shelf: Choose your columns wisely

Only pull the columns you need; including unnecessary ones will affect readability and performance:

SELECT t.id, t.date_column, t.other_column
...


Explicit column selection enhances maintainability and boosts query performance.

When too much is too much: Handle duplicates

Stroll in the DISTINCT park when your table could have identical entries sharing the same date:

SELECT DISTINCT t.id, t.date_column, ...
...
-- Because no one likes déjà vu in their results


This lil' trick helps you wave goodbye to irksome duplicate group entries.

Performance traps are a no-no: Use JOIN, not IN and NOT IN

NOT IN and IN operations with subqueries can really drag on large tables. Maintain optimal query speed with JOIN operations or window functions.

Unleash the power of derived tables

When you need to perform additional computations or filtering based on the subquery results, pull a rabbit out of the SQL hat and build a derived table from your main query:

SELECT derived.*
FROM (
    SELECT id, date_column, ...
    ...
) AS derived
...


Test before deploying in the wild

Use SQL Fiddle to experiment and benchmark your statements against different database systems for efficiency. No one deserves painfully slow SQL queries.