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

Re: [SQL] Result set granularity..

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Rasmus Aveskogh <rasmus(at)defero(dot)se>
Cc: <pgsql-sql(at)postgresql(dot)org>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: [SQL] Result set granularity..
Date: 2003-09-29 19:33:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-sql
On Sat, 27 Sep 2003, Rasmus Aveskogh wrote:

> Hi,
> Since I went from Oracle to PostgreSQL I've been missing the "invisable"
> column 'rownum'. I often used it to lower the granularity of my data.
> For example, say I have a large table containing some sort of statistical
> data and want to plot a graph using it.
> If the graph is 600 pixels wide I might as well lower the granularity of
> my incoming data to 600 measure points before plotting.
> In Oracle I used to do this by using the modulus operator on the rownum
> column as a restriction.
> SELECT <column> FROM <table> WHERE mod(rownum, 5) = 0;
> The query above would give me every fifth row of the original result set
> and would save me from sending the data over my database connection and do
> the lowering of the granularity in the application.
> I have two questions, one dependent on the answer on the other one..
> 1) Is it possible to achieve this any other _easy_ way?
> Perhaps it would be feasible to write a wrapper using a counter which
> makes the SELECT and then return every fifth row to the SELECT calling the
> wrapper. But then I assume the data still has to "travel" one step which
> puts on some overhead.

The standard "trick" in Postgresql is to create a temporary sequence and 
select that as well:

create temp sequence aaa;
postgres=# select *, nextval('aaa') from accounts;

to get a row number.  The sequence creation and use is pretty fast, and 
I've used it before.  

> 2) Would it be possible to add one more limit argument to the non-standard
> set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would
> return every n row of the initial result set. I think that would be
> gladly accepted for folks working with statistical data.

Using the same trick, you can get every 5th row like this:

select * from (select *, nextval('aaa') as row from accounts) as a where a.row%5=4;

In response to

pgsql-sql by date

Next:From: Ben SchneiderDate: 2003-09-29 19:50:20
Subject: Help with pivoting tables
Previous:From: Josh BerkusDate: 2003-09-29 19:20:20
Subject: Re: Problems to be solved as soon as possible

pgsql-general by date

Next:From: Tom LaneDate: 2003-09-29 19:38:25
Subject: Re: Time problem again?
Previous:From: Tom LaneDate: 2003-09-29 19:19:55
Subject: Re: Postgres vs. Progress performance

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