Re: Overlapping Ranges- Query Alternative - Mailing list pgsql-sql

From Andreas Gaab
Subject Re: Overlapping Ranges- Query Alternative
Date
Msg-id 48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server
Whole thread Raw
In response to Overlapping Ranges- Query Alternative  ("Ozer, Pam" <pozer@automotive.com>)
Responses Re: Overlapping Ranges- Query Alternative
Re: Overlapping Ranges- Query Alternative
List pgsql-sql

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@postgresql.org [mailto:pgsql-sql-owner@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

 

pgsql-sql by date:

Previous
From: "Ozer, Pam"
Date:
Subject: Overlapping Ranges- Query Alternative
Next
From: "Marc Mamin"
Date:
Subject: Re: Overlapping Ranges- Query Alternative