Search Postgresql Archives

Re: SELECTing every Nth record for better performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



In response to Tom :
> I have a big table that is used for datalogging. I'm designing
> graphing interface that will visualise the data. When the user is
> looking at a small daterange I want the database to be queried for all
> records, but when the user is 'zoomed out', looking at an overview, I
> want run a query that skips every nth record and returns a managable
> dataset that still gives a correct overview of the data without
> slowing the programme down. Is there an easy way to do this that I
> have overlooked? I looked at:


Do you have 8.4? If yes:

test=# create table data as select s as s from generate_Series(1,1000) s;
SELECT



test=*# select s from (select *, row_number() over (order by s) from
data) foo where row_number % 3 = 0 limit 10;
 s
----
  3
  6
  9
 12
 15
 18
 21
 24
 27
 30
(10 rows)


-- or skip every 5. record:

test=*# select s from (select *, row_number() over (order by s) from
data) foo where row_number % 5 != 0 limit 10;
 s
----
  1
  2
  3
  4
  6
  7
  8
  9
 11
 12
(10 rows)


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux