Loupi Loupi -5 years ago 103
SQL Question

Get values from a table with witn an interval of 20, 10, 5

Hi I don't know if this was asked before but I would like to get the values from a table of id's and numbers (the numbers are ranged from 1 - 190) with a given interval of 20, 10 and 5.

If I set the interval to 10 then I would get number values like 10, 20, 30, 40... 190 and if I set it to 20 then I would get values like 20, 40, 60 and so on.

I've tried to use for loops but performance and speed wise is not very efficient.

I'm using PHP to query data in Postgres.

Here's my sample codes:

$interval = intval($_REQUEST['interval']);

$sql_last = 'SELECT MAX(elevation) AS elev FROM "Contour"';
$result_last = pg_prepare($conn, 'query_last', $sql_last);
$result_last = pg_execute($conn, 'query_last', array());

$row_last = pg_fetch_array($result_last, NULL, PGSQL_ASSOC);
$max_elev = $row_last['elev'];

for($x = $interval; $x < $max_elev; $x + $interval) {
$sql = 'SELECT id, elevation FROM "Contour" WHERE elevation = $1';
$result = pg_prepare($conn, $x, $sql);
$result = pg_execute($conn, $x, array($x));


Answer Source

If you want to generate a series with these values, you can do:

select generate_series(0, 190, 10) as val

If you want to include this in a query like yours:

select c.*
from contour c join
     (select generate_series(min(c2.elev), max(c2.elev), 10) as elev
      from contour c2
     ) cc
     on c.elevation = c2.elev;

You might want to replace min(c2.elev) with 0.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download