Обсуждение: [NOVICE] Please advice on a query

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