While we could always check for the query performance reasons, I rather think that this is an overkill for the purpose of mere line numbers.
If such queries don't change frequently, you could be better off using a simple function that instead adds a 'rownumber' field to the output of the inner SQL query. The 'rownumber' could instead be calculated by simply incrementing it within a FOR loop for each row. *Robins* On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis < [EMAIL PROTECTED]> wrote: > Hi Everyone, > > After spending some time searching through our good friend Mr. Google and > the mailing list I found a post that provided me with a query that does just > what I need. > > However, the query takes FOREVER and although this is stated in the > original mail I thought I would ask if there was any advice that you might > be able to provide to speed things up a little. > > And while the query does return over 27,000 rows in my case, I didn't > expect it to take 9 minutes and 11 seconds! > > Please find the query below - and of course - thanks in advance for any > assistance you might be able to provide me with! > > select > (select > count(*) > from > myTable as myCount > where > myCount.contactdate <= myTable.contactdate > ) as rownum, > contactdate > from > myTable > where > contactdate > '2007-06-30 23:59:59' > order by > contactdate; > > > -Gavin > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
