pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Andreas Gaab <A.Gaab@scanlab.de>
To: 'Ozer, Pam' <pozer@automotive.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Overlapping Ranges- Query Alternative
Date: Fri, 12 Nov 2010 09:23:05 +0100
Message-ID: <48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server> (raw)
In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D101839A@mail-001.corp.automotive.com>
References: <216FFB77CBFAEE4B8EE4DF0A939FF1D101839A@mail-001.corp.automotive.com>

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

view thread (4+ messages)  latest in thread

Message-ID: <48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server>
Permalink:  ../48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server/
Also on:    postgresql.org/message-id/48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: A.Gaab@scanlab.de, pozer@automotive.com
  Subject: Re: Overlapping Ranges- Query Alternative
  In-Reply-To: <48DA836F3865C54B8FBF424A3B775AF6012D11337B@Exchange-Server>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox