pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery with LIMIT clause
4+ messages / 2 participants
[nested] [flat]
* Query with LIMIT clause
@ 2012-09-09 17:25 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: JORGE MALDONADO @ 2012-09-09 17:25 UTC (permalink / raw)
To: pgsql-sql
I have the following records that I get from a query, the fields are date
type in day/month/year format:
-------------------------------------
Initial Final
Date Date
-------------------------------------
27/08/2012 04/09/2012
29/08/2012 09/09/2012
28/08/2012 09/09/2012
30/08/2012 09/09/2012
30/08/2012 09/09/2012
27/08/2012 09/09/2012
31/08/2012 09/09/2012
28/08/2012 10/09/2012
05/09/2012 16/09/2012
As you can see, this result is ordered by Final Date. What I need is to get
the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I
have tried an ORDEY BY DESC but the result is the same. I will very much
appreciate your comments.
Respectfully,
Jorge Maldonado
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query with LIMIT clause
@ 2012-09-09 17:45 David Johnston <polobo@yahoo.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: David Johnston @ 2012-09-09 17:45 UTC (permalink / raw)
To: 'JORGE MALDONADO' <jorgemal1960@gmail.com>; pgsql-sql
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of JORGE MALDONADO
Sent: Sunday, September 09, 2012 1:26 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Query with LIMIT clause
I have the following records that I get from a query, the fields are date
type in day/month/year format:
-------------------------------------
Initial Final
Date Date
-------------------------------------
27/08/2012 04/09/2012
29/08/2012 09/09/2012
28/08/2012 09/09/2012
30/08/2012 09/09/2012
30/08/2012 09/09/2012
27/08/2012 09/09/2012
31/08/2012 09/09/2012
28/08/2012 10/09/2012
05/09/2012 16/09/2012
As you can see, this result is ordered by Final Date. What I need is to get
the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I
have tried an ORDEY BY DESC but the result is the same. I will very much
appreciate your comments.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
First, you should really order by both columns, not just "final date". The
ties on 9/9/12 are unordered.
Second, you will indeed need to reverse the sort order and then take the
first 5 records; figuring out and limiting on the last 5 isn't worth the
effort.
SELECT initial_date, final_date
FROM date_source
ORDER BY final_date DESC, initial_date DESC
LIMIT 5
You can put the above into a sub-query and re-order if the final result is
needed in ascending order.
If this doesn't seem to work you will want to provide the exact
query/queries you are trying so that someone may spot what you are doing
wrong.
Dave
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query with LIMIT clause
@ 2012-09-10 00:56 JORGE MALDONADO <jorgemal1960@gmail.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 4+ messages in thread
From: JORGE MALDONADO @ 2012-09-10 00:56 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql
Firstly, who should I reply to, you or the list?
Your solution is working pretty fine, I appreciate your advice. Now, I am
sure that an index is a good idea in order to make the query fast. What
would be a good criteria to define an index? Will an index for final date
and another for initial date is the choice, or one index composed of both
initial and final date?
Regards,
Jorge Maldonado
On Sun, Sep 9, 2012 at 12:45 PM, David Johnston <polobo@yahoo.com> wrote:
> From: pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org]
> On Behalf Of JORGE MALDONADO
> Sent: Sunday, September 09, 2012 1:26 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Query with LIMIT clause
>
> I have the following records that I get from a query, the fields are date
> type in day/month/year format:
>
> -------------------------------------
> Initial Final
> Date Date
> -------------------------------------
> 27/08/2012 04/09/2012
> 29/08/2012 09/09/2012
> 28/08/2012 09/09/2012
> 30/08/2012 09/09/2012
> 30/08/2012 09/09/2012
> 27/08/2012 09/09/2012
> 31/08/2012 09/09/2012
> 28/08/2012 10/09/2012
> 05/09/2012 16/09/2012
>
> As you can see, this result is ordered by Final Date. What I need is to get
> the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I
> have tried an ORDEY BY DESC but the result is the same. I will very much
> appreciate your comments.
>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
>
> First, you should really order by both columns, not just "final date". The
> ties on 9/9/12 are unordered.
>
> Second, you will indeed need to reverse the sort order and then take the
> first 5 records; figuring out and limiting on the last 5 isn't worth the
> effort.
>
> SELECT initial_date, final_date
> FROM date_source
> ORDER BY final_date DESC, initial_date DESC
> LIMIT 5
>
> You can put the above into a sub-query and re-order if the final result is
> needed in ascending order.
>
> If this doesn't seem to work you will want to provide the exact
> query/queries you are trying so that someone may spot what you are doing
> wrong.
>
> Dave
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query with LIMIT clause
@ 2012-09-10 01:49 David Johnston <polobo@yahoo.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Johnston @ 2012-09-10 01:49 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
Always reply to list. It is also preferred to bottom-post.
Indexes are never simple answers and there isn't enough info to really give good advice here.
You should try different versions and estimate performance (read and write). My guess is that a compound index (2 columns) would work well though if you are going to filter/sort on initial date frequently (by itself) then it likely wants its own index anyway and having two separate indexes would be better.
David J.
On Sep 9, 2012, at 20:56, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> Firstly, who should I reply to, you or the list?
> Your solution is working pretty fine, I appreciate your advice. Now, I am sure that an index is a good idea in order to make the query fast. What would be a good criteria to define an index? Will an index for final date and another for initial date is the choice, or one index composed of both initial and final date?
>
> Regards,
> Jorge Maldonado
>
> On Sun, Sep 9, 2012 at 12:45 PM, David Johnston <polobo@yahoo.com> wrote:
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
> On Behalf Of JORGE MALDONADO
> Sent: Sunday, September 09, 2012 1:26 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Query with LIMIT clause
>
> I have the following records that I get from a query, the fields are date
> type in day/month/year format:
>
> -------------------------------------
> Initial Final
> Date Date
> -------------------------------------
> 27/08/2012 04/09/2012
> 29/08/2012 09/09/2012
> 28/08/2012 09/09/2012
> 30/08/2012 09/09/2012
> 30/08/2012 09/09/2012
> 27/08/2012 09/09/2012
> 31/08/2012 09/09/2012
> 28/08/2012 10/09/2012
> 05/09/2012 16/09/2012
>
> As you can see, this result is ordered by Final Date. What I need is to get
> the last 5 records only. If I use the LIMIT 5 clause I get the first 5. I
> have tried an ORDEY BY DESC but the result is the same. I will very much
> appreciate your comments.
>
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>.
>
> First, you should really order by both columns, not just "final date". The
> ties on 9/9/12 are unordered.
>
> Second, you will indeed need to reverse the sort order and then take the
> first 5 records; figuring out and limiting on the last 5 isn't worth the
> effort.
>
> SELECT initial_date, final_date
> FROM date_source
> ORDER BY final_date DESC, initial_date DESC
> LIMIT 5
>
> You can put the above into a sub-query and re-order if the final result is
> needed in ascending order.
>
> If this doesn't seem to work you will want to provide the exact
> query/queries you are trying so that someone may spot what you are doing
> wrong.
>
> Dave
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-09-10 01:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-09 17:25 Query with LIMIT clause JORGE MALDONADO <jorgemal1960@gmail.com>
2012-09-09 17:45 ` David Johnston <polobo@yahoo.com>
2012-09-10 00:56 ` JORGE MALDONADO <jorgemal1960@gmail.com>
2012-09-10 01:49 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox