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.

回答1:

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.

回答2:

Simple version with EXISTS semi-join:

SELECT note FROM tbl t
WHERE  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 t
WHERE  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 note
FROM  (
    SELECT note, row_number() OVER (ORDER BY created_at DESC, id DESC) AS rn
    FROM   tbl t 
    ) x
WHERE  rn = 1;

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

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

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

SQL Fiddle.

回答3:

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
)

回答4:

I may not know how to answer on this platform but what I have suggested is working

SELECT * FROM table GROUP BY field ORDER BY max(field) DESC;

You can get the last value of the field without limiting, usually in JOINED query we get the last update time with no limiting of output like this way, such as last message time without limiting it.