SQL SELECT last entry without limiting

Simple version with EXISTS semi-join:

SELECT note FROM tbl tWHERE  NOT EXISTS    (SELECT 1 FROM tbl t1 WHERE t1.created_at > t.created_at);

"Find a note where no other note was created later."

This shares the weakness of @Hogan's version that it can return multiple rows if created_at is not UNIQUE - like @Ollie already pointed out. Unlike @Hogan's query (max() is only defined for simple types) this one can be improved easily:

Compare row types

SELECT note FROM tbl tWHERE  NOT EXISTS   (SELECT 1 FROM tbl t1    WHERE  (t1.created_at, t1.id) > (t.created_at, t.id));

Assuming you want the greatest id in case of a tie with created_at, and id is the primary key, therefore unique. This works in PostgreSQL and MySQL.

SQL Fiddle.

Window function

The same can be achieved with a window function in PostgreSQL:

SELECT noteFROM  (    SELECT note, row_number() OVER (ORDER BY created_at DESC, id DESC) AS rn    FROM   tbl t     ) xWHERE  rn = 1;

MySQL lacks support for window functions. You can substitute with a variable like this:

SELECT noteFROM  (    SELECT note, @rownum := @rownum + 1 AS rn    FROM   tbl t          ,(SELECT @rownum := 0) r    ORDER  BY created_at DESC, id DESC    ) xWHERE  rn = 1;

(SELECT @rownum := 0) r initializes the variable with 0 without an explicit SET command.

SQL Fiddle.

If your id column is an autoincrementing primary key field, it's pretty easy. This assumes the latest note has the highest id. (That might not be true; only you know that!)

select *  from note where id = (select max(id) from note)

It's here: http://sqlfiddle.com/#!2/7478a/1/0 for MySQL and here http://sqlfiddle.com/#!1/6597d/1/0 for postgreSQL. Same SQL.

If your id column isn't set up so the latest note has the highest id, but still is a primary key (that is, still has unique values in each row), it's a little harder. We have to disambiguate identical dates; we'll do this by choosing, arbitrarily, the highest id.

select *  from note  where id = (              select max(id)                from note where created_at =                    (select max(created_at)                       from note                   )              )

Here's an example: http://sqlfiddle.com/#!2/1f802/4/0 for MySQL.Here it is for postgreSQL (the SQL is the same, yay!) http://sqlfiddle.com/#!1/bca8c/1/0

Another possibility: maybe you want both notes shown together in one row if they were both created at the same exact time. Again, only you know that.

select group_concat(note separator '; ')   from note  where created_at = (select max(created_at) from note)

In postgreSQL 9+, it's

 select string_agg(note, '; ')    from note   where created_at = (select max(created_at) from note)

If you do have the possibility for duplicate created_at times and duplicate id values, and you don't want the group_concat effect, you are unfortunately stuck with LIMIT.

I'm not 100% on Postgres (actually never used it) but you can get the same effect with something like this - if the created_at is unique ... (or with any column which is unique):

SELECT note FROM table WHERE created_at = (    SELECT MAX(created_at) FROM table)