Please advice on a query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Please advice on a query

JORGE MALDONADO
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
Reply | Threaded
Open this post in threaded view
|

Re: Please advice on a query

Efraín Déctor
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 ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
Reply | Threaded
Open this post in threaded view
|

Re: Please advice on a query

Lutz Horn
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-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
(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-09
 2017-11-09
 2017-11-09
 2017-11-09
(4 rows)

Regards

Lutz



signature.asc (957 bytes) Download Attachment
Previous Thread Next Thread