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

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 (view raw, whole thread or download thread mbox)
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

pgsql-sql by date

Next:From: Joshua TolleyDate: 2010-11-12 15:50:41
Subject: Re: "slow lock" log in addition tolog_min_duration_statement ?
Previous:From: Andreas GaabDate: 2010-11-12 08:23:05
Subject: Re: Overlapping Ranges- Query Alternative

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