Re: Query with LIMIT clause - Mailing list pgsql-sql

From JORGE MALDONADO
Subject Re: Query with LIMIT clause
Date
Msg-id CAAY=A79GLHa=9TEyuT5NM0a+1wuwqryFVe3i__6P_UQU9yA+YQ@mail.gmail.com
Whole thread Raw
In response to Re: Query with LIMIT clause  ("David Johnston" <polobo@yahoo.com>)
Responses Re: Query with LIMIT clause
List 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



pgsql-sql by date:

Previous
From: James Sharrett
Date:
Subject: returning values from dynamic SQL to a variable
Next
From: David Johnston
Date:
Subject: Re: Query with LIMIT clause