Re: [SQL] Overlapping Ranges- Query Alternative

[Andreas Gaab]

Hi,

the following works:

Create temp table ranges (Groups int, ColumnA int);
Insert into ranges Values(2,45);
Insert into ranges Values(3,15);
Insert into ranges Values(4,25);
Insert into ranges Values(5,35);

Select Groups,
Case when ColumnA between 0 and 19 then 0
     when ColumnA >=20 AND ColumnA < 30 then generate_series(20,20,10)
     when ColumnA >=30 AND ColumnA < 40 then generate_series(20,30,10)
     when ColumnA>=40 AND ColumnA < 50 then generate_series(20,40,10)
     when ColumnA>=50 then generate_series(20,50,10) end MinRange
from ranges;


--or even only

Select Groups,
CASE WHEN ColumnA < 20 then 0 ELSE
generate_series(20, (floor(ColumnA / 10.0) * 10)::integer ,10) END MinRange
from ranges;


Best, Andreas

Von: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] Im 
Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alternative

I have the following problem:

Create temp table ranges (Groups int, ColumnA int);
Insert into ranges
Values(2,45);

Select Groups,
Case when ColumnA between 0 and 19 then 0
     when ColumnA >=20 then 20
     when ColumnA >=30 then 30
     when ColumnA>=40 then 40
     when ColumnA>=50 then 50 end MinRange
from ranges

Results:
Groups minrange
2;20

What I want Is : One column can fall into multiple ranges.  For example 45 >20, 
30, and 40 so I want the following results
2;20
2;30
2;40

I know I could do a union with each range but is there any way to bring back 
all ranges in one query?  I need to bring back the values in one column so 
having separate columns for each range is not an option.

Thank you in advance for any help

Pam Ozer