Thread: [NOVICE] Please advice on a query
I have a table with a date field and I need to get all of the records with the most recent date.
For example, if I have the following records, I need to get all of them with date 2017-11-09 only. which represent the most recent.
What would be a good approach?
2017-01-01
2017-01-01
2017-10-15
2017-10-15
2017-10-15
2017-11-09
2017-11-09
2017-11-09
2017-11-09
I thought about issuing a SELECT DISTINCT to get one record for each date, ORDER BY date DESC and then LIMIT 1 so I finally get the most recent date and use it in the WHERE clause. But this seems to me a bit complex.
Respectfully,
Jorge Maldonado
Hello. You could use a window function: https://www.postgresql.org/docs/current/static/functions-window.html El 14/11/2017 a las 11:04 a. m., JORGE MALDONADO escribió: > I have a table with a date field and I need to get all of the records > with the most recent date. > For example, if I have the following records, I need to get all of > them with date 2017-11-09 only. which represent the most recent. > > What would be a good approach? > > 2017-01-01 > 2017-01-01 > 2017-10-15 > 2017-10-15 > 2017-10-15 > 2017-11-09 > 2017-11-09 > 2017-11-09 > 2017-11-09 > > I thought about issuing a SELECT DISTINCT to get one record for each > date, ORDER BY date DESC and then LIMIT 1 so I finally get the most > recent date and use it in the WHERE clause. But this seems to me a bit > complex. > > Respectfully, > Jorge Maldonado -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Hi, Am 14.11.17 um 18:19 schrieb Efraín Déctor: > You could use a window function: > https://www.postgresql.org/docs/current/static/functions-window.html Like this: lutz=> \d dates Table "pg_temp_3.dates"Column | Type | Modifiers --------+------+-----------d | date | lutz=> select * from dates; d ------------2017-01-012017-01-012017-10-152017-10-152017-10-152017-11-092017-11-092017-11-092017-11-09 (9 rows) lutz=> select d from ( select d, rank() over (order by d desc) as r from dates ) as sq where r = 1; d ------------2017-11-092017-11-092017-11-092017-11-09 (4 rows) Regards Lutz