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.
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:
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.
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.
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
)
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.
