Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 718DF1337BC7 for ; Fri, 12 Nov 2010 04:31:56 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 63025-03-7 for ; Fri, 12 Nov 2010 08:31:49 +0000 (UTC) X-Greylist: delayed 00:08:30.242771 by SQLgrey-1.7.6 Received: from mail.scanlab.de (mail.scanlab.de [62.138.55.241]) by mail.postgresql.org (Postfix) with ESMTP id 5A8301337BEF for ; Fri, 12 Nov 2010 04:31:39 -0400 (AST) Received: from unknown (HELO Exchange-Server.scanlab-intern.de) ([172.16.20.15]) by mail.scanlab-intern.de with ESMTP; 12 Nov 2010 09:23:06 +0100 Received: from Exchange-Server.scanlab-intern.de ([127.0.0.1]) by Exchange-Server ([127.0.0.1]) with mapi; Fri, 12 Nov 2010 09:23:05 +0100 From: Andreas Gaab To: "'Ozer, Pam'" , "pgsql-sql@postgresql.org" Date: Fri, 12 Nov 2010 09:23:05 +0100 Subject: Re: Overlapping Ranges- Query Alternative Thread-Topic: Overlapping Ranges- Query Alternative Thread-Index: AcuB060MB+DP6GVpQ1u0PWGYw099YAAboc3A Message-ID: <48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server> References: <216FFB77CBFAEE4B8EE4DF0A939FF1D101839A@mail-001.corp.automotive.com> In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D101839A@mail-001.corp.automotive.com> Accept-Language: de-DE Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: de-DE Content-Type: multipart/alternative; boundary="_000_48DA836F3865C54B8FBF424A3B775AF6012D11337BExchangeServe_" MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.01 tagged_above=-5 required=5 tests=BAYES_20=-0.001, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/17 X-Sequence-Number: 34755 --_000_48DA836F3865C54B8FBF424A3B775AF6012D11337BExchangeServe_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 >=3D20 AND ColumnA < 30 then generate_series(20,20,10) when ColumnA >=3D30 AND ColumnA < 40 then generate_series(20,30,10) when ColumnA>=3D40 AND ColumnA < 50 then generate_series(20,40,10) when ColumnA>=3D50 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 >=3D20 then 20 when ColumnA >=3D30 then 30 when ColumnA>=3D40 then 40 when ColumnA>=3D50 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 bac= k all ranges in one query? I need to bring back the values in one column s= o having separate columns for each range is not an option. Thank you in advance for any help Pam Ozer --_000_48DA836F3865C54B8FBF424A3B775AF6012D11337BExchangeServe_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi,

=

 

the following work= s:

 

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 >=3D20 AND ColumnA < 30 then generate_series(20,2= 0,10)

   &nb= sp; when ColumnA >=3D30 AND ColumnA < 40 then generate_series(20,30,1= 0)

    = when ColumnA>=3D40 AND ColumnA < 50 then generate_series(20,40,10)

     whe= n ColumnA>=3D50 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

 <= /span>

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: pgsq= l-sql@postgresql.org
Betreff: [SQL] Overlapping Ranges- Query Alt= ernative

 <= /o:p>

I have the following probl= em:

 = ;

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

     w= hen ColumnA >=3D20 then 20

     when ColumnA >=3D30 then 30

   = ;  when ColumnA>=3D40 then 40

     when ColumnA>=3D50 then= 50 end MinRange

from ranges

 

Result= s:

Groups min= range

2;20

 =

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

<= span lang=3DEN-US>2;20

2;30

= 2;40

&nbs= p;

I know I could d= o 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 se= parate columns for each range is not an option.

 

Thank you in advance for any help

 

=

Pam Ozer

=  

= --_000_48DA836F3865C54B8FBF424A3B775AF6012D11337BExchangeServe_--