Search code examples
mysqlsqlpostgresqllimit

SQL SELECT last entry without limiting

I have a table with 3 fields:

id
note
created_at

Is there a way in the SQL language especially Postgres that I can select the value of the last note without having to LIMIT 1?

Normal query:

select note from table order by created_at desc limit 1

I'm interested in something avoiding the limit since I'll need it as a subquery.

Solution

  • 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.