Re: Overlapping Ranges- Query Alternative

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: "Andreas Gaab" <A(dot)Gaab(at)scanlab(dot)de>, "Ozer, Pam" <pozer(at)automotive(dot)com>, <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Overlapping Ranges- Query Alternative
Date: 2010-11-12 08:57:48
Message-ID: C4DAC901169B624F933534A26ED7DF31034BB8DC@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Joshua Tolley 2010-11-12 15:50:41 Re: "slow lock" log in addition to log_min_duration_statement ?
Previous Message Andreas Gaab 2010-11-12 08:23:05 Re: Overlapping Ranges- Query Alternative