pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron St-Pierre <rstpierre@syscor.com>
To: pgsql-performance@postgresql.org
Subject: ORDER BY and LIMIT with SubSelects
Date: Wed, 21 Jan 2004 09:18:18 -0800
Message-ID: <400EB45A.40309@syscor.com> (raw)

I need to get 200 sets of the most recent data from a table for further 
processing, ordered by payDate. My
current solution is to use subselects to:
1 - get a list of unique data
2 - get the 200 most recent records (first 200 rows, sorted descending)
3 - sort them in ascending order

SELECT SSS.* FROM
 (SELECT SS.* FROM
   (SELECT DISTINCT ON (nonUniqField)
    first, second, third, cost, payDate, nonUniqField
    FROM histdata
    WHERE userID = 19048 AND cost IS NOT NULL
   )
  SS  
  ORDER BY SS.payDate DESC LIMIT 200
) SSS
ORDER BY payDate;

My question is in regards to steps 2 and 3 above. Is there some way that 
I can combine both steps into one to save some time?

PostgreSQL 7.4beta2 on i686-pc-linux-gnu, compiled by GCC 2.95.4

Thanks
Ron





view thread (2+ messages)  latest in thread

Message-ID: <400EB45A.40309@syscor.com>
Permalink:  ../400EB45A.40309@syscor.com/
Also on:    postgresql.org/message-id/400EB45A.40309@syscor.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rstpierre@syscor.com
  Subject: Re: ORDER BY and LIMIT with SubSelects
  In-Reply-To: <400EB45A.40309@syscor.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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