Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TAt7h-0007wL-1U for pgsql-sql@postgresql.org; Mon, 10 Sep 2012 01:49:13 +0000 Received: from nm17.bullet.mail.ac4.yahoo.com ([98.139.52.214]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TAt7d-0000jk-ES for pgsql-sql@postgresql.org; Mon, 10 Sep 2012 01:49:12 +0000 Received: from [98.139.52.190] by nm17.bullet.mail.ac4.yahoo.com with NNFMP; 10 Sep 2012 01:49:07 -0000 Received: from [98.139.52.187] by tm3.bullet.mail.ac4.yahoo.com with NNFMP; 10 Sep 2012 01:49:07 -0000 Received: from [127.0.0.1] by omp1070.mail.ac4.yahoo.com with NNFMP; 10 Sep 2012 01:49:07 -0000 X-Yahoo-Newman-Id: 608412.69401.bm@omp1070.mail.ac4.yahoo.com Received: (qmail 66363 invoked from network); 10 Sep 2012 01:49:07 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=ycm7YCwPzaDw9/YEkkmiY08IHroTYlDNos4IDclqhsfecESTWGHVg7Ol1CZ2ZXm3L2UKA9CMqTMwr1kZhb+2JN3MWihsr0aTnInIpmNlKSxvrv9SltT1ziZsoX3plAjcoEZeJuAW0q+rFiT3PBr53nRKKw/yoq1q+P9hujfobKo= ; DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1347241747; bh=RPcw27N0pqiqccplf7qGBWXkI9mAx8x4W+EqPZXFJYI=; h=X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To; b=ZivM32tAPoBNLunpoWQUcw8eXawzNqCZXw9qlZB6KpIPvME3x2Lv6aQwoSAAQh5LVQ3HTqX1WnJBoUSG//m15NtC4fOdx5t3nEb7Shh6YDMNAyvFLzb1PYADSz5S/zv6vGMxj15wuPbYrhzIwnFiR+sxn9mxOKGsKfJmZ/XkgUg= X-Yahoo-Newman-Property: ymail-3 X-YMail-OSG: g2rcv0QVM1nSvfpycpRPEP.vl8so_vstavaENEL3Gf8jQQv O9hZ_n0H3HmM_PTUJwub0HFwtE7aX_qDxpwl7.WR3rkZ0i49n3cvQT5TglxE aRJJo9Tj7n8r5EimMrN5ETaWJaN35XD.OaMiH_uxYChsulr9pFevSmSlJ4wg Qy2tFVDOlTlIA.Wpu8_ijFN5HuE1eEHSKOUSFxU4pPoOXRdA9VMzHw0CYn6n W8RMcwBzggo9wddalBsc7yQh43D7zd6s8oOXYZgvwYIxax72cpQ4zmXmrtLm AnA1h0mBJmAYhPPxBRqwMwe.MZjab.21tTzEFcg0ZHA5IExnJWgmAbGxzhHt kG7sLY4hJdTODg7tHWEoXUni2Yp.yb0Xrn9FFHCiOQpqFXq2ZGFcYeDj1igF gcXMPJmGqzIGKDEBg0t4gxsr9hNHtFWqoyU4u X-Yahoo-SMTP: mpGJl6eswBD2IBufoVEg0Pa8gg-- Received: from [192.12.17.101] (polobo@24.93.23.188 with xymcookie) by smtp117-mob.biz.mail.ac4.yahoo.com with SMTP; 09 Sep 2012 18:49:07 -0700 PDT References: <019f01cd8eb2$e4f4be20$aede3a60$@yahoo.com> In-Reply-To: Mime-Version: 1.0 (1.0) Content-Transfer-Encoding: 7bit Content-Type: multipart/alternative; boundary=Apple-Mail-0B377613-6382-4367-96ED-8B136ADD0DED Message-Id: <70062960-5BDF-44B3-89EF-C30C204D94B8@yahoo.com> Cc: "pgsql-sql@postgresql.org" X-Mailer: iPad Mail (9B206) From: David Johnston Subject: Re: Query with LIMIT clause Date: Sun, 9 Sep 2012 21:49:09 -0400 To: JORGE MALDONADO X-Pg-Spam-Score: -2.4 (--) X-Archive-Number: 201209/19 X-Sequence-Number: 36821 --Apple-Mail-0B377613-6382-4367-96ED-8B136ADD0DED Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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 g= ood 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 yo= u are going to filter/sort on initial date frequently (by itself) then it li= kely wants its own index anyway and having two separate indexes would be bet= ter. David J. On Sep 9, 2012, at 20:56, JORGE MALDONADO wrote: > Firstly, who should I reply to, you or the list? > Your solution is working pretty fine, I appreciate your advice. Now, I am s= ure 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 ano= ther for initial date is the choice, or one index composed of both initial a= nd final date? >=20 > Regards, > Jorge Maldonado >=20 > On Sun, Sep 9, 2012 at 12:45 PM, David Johnston wrote: > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.or= g] > On Behalf Of JORGE MALDONADO > Sent: Sunday, September 09, 2012 1:26 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Query with LIMIT clause >=20 > I have the following records that I get from a query, the fields are date > type in day/month/year format: >=20 > ------------------------------------- > 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 >=20 > As you can see, this result is ordered by Final Date. What I need is to ge= t > 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. >=20 > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>. >=20 > First, you should really order by both columns, not just "final date". Th= e > ties on 9/9/12 are unordered. >=20 > 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. >=20 > SELECT initial_date, final_date > FROM date_source > ORDER BY final_date DESC, initial_date DESC > LIMIT 5 >=20 > You can put the above into a sub-query and re-order if the final result is= > needed in ascending order. >=20 > 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. >=20 > Dave >=20 >=20 >=20 --Apple-Mail-0B377613-6382-4367-96ED-8B136ADD0DED Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
Always reply to list. &nbs= p;It is also preferred to bottom-post.

Indexes are n= ever simple answers and there isn't enough info to really give good advice h= ere.

You should try different versions and estimate= performance (read and write).  My guess is that a compound index (2 co= lumns) would work well though if you are going to filter/sort on initial dat= e frequently (by itself) then it likely wants its own index anyway and havin= g 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 you= r advice. Now, I am sure that an index is a good idea in order to make the q= uery fast. What would be a good criteria to define an index? Will an index f= or final date and another for initial date is the choice, or one index compo= sed of both initial and final date?

Regards,
Jorge Maldonado

On Sun, Sep 9, 2012 at 12:45 PM, David Johnston <polobo@yahoo.co= m> 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<= br> 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



= --Apple-Mail-0B377613-6382-4367-96ED-8B136ADD0DED--