Skip site navigation (1) Skip section navigation (2)

ORDER BY and LIMIT with SubSelects

From: Ron St-Pierre <rstpierre(at)syscor(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: ORDER BY and LIMIT with SubSelects
Date: 2004-01-21 17:18:18
Message-ID: 400EB45A.40309@syscor.com (view raw or flat)
Thread:
Lists: pgsql-performance
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



Responses

pgsql-performance by date

Next:From: ArnauDate: 2004-01-21 18:25:15
Subject: Queries with timestamps
Previous:From: Stephan SzaboDate: 2004-01-21 16:45:00
Subject: Re: Trigger question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group