Re: Overlapping Ranges- Query Alternative

or:

Select Groups, generate_series

FROM

ranges JOIN generate_series(10,50,10) on ( ColumnA < generate_series)

ORDER by Groups , generate_series

;

regards,

Marc Mamin

From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andreas Gaab
Sent: Freitag, 12. November 2010 09:23
To: 'Ozer, Pam'; pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Overlapping Ranges- Query Alternative

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-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] Im Auftrag von Ozer, Pam
Gesendet: Donnerstag, 11. November 2010 20:07
An: pgsql-sql(at)postgresql(dot)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