pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOverlapping Ranges- Query Alternative
4+ messages / 3 participants
[nested] [flat]
* Overlapping Ranges- Query Alternative
@ 2010-11-11 19:07 Ozer, Pam <pozer@automotive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ozer, Pam @ 2010-11-11 19:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Overlapping Ranges- Query Alternative
@ 2010-11-12 08:23 Andreas Gaab <A.Gaab@scanlab.de>
parent: Ozer, Pam <pozer@automotive.com>
0 siblings, 2 replies; 4+ messages in thread
From: Andreas Gaab @ 2010-11-12 08:23 UTC (permalink / raw)
To: 'Ozer, Pam' <pozer@automotive.com>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Overlapping Ranges- Query Alternative
@ 2010-11-12 08:57 Marc Mamin <M.Mamin@intershop.de>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 0 replies; 4+ messages in thread
From: Marc Mamin @ 2010-11-12 08:57 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; Ozer, Pam <pozer@automotive.com>; 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@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Freitag, 12. November 2010 09:23
To: 'Ozer, Pam'; pgsql-sql@postgresql.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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Overlapping Ranges- Query Alternative
@ 2010-11-12 16:36 Ozer, Pam <pozer@automotive.com>
parent: Andreas Gaab <A.Gaab@scanlab.de>
1 sibling, 0 replies; 4+ messages in thread
From: Ozer, Pam @ 2010-11-12 16:36 UTC (permalink / raw)
To: Andreas Gaab <A.Gaab@scanlab.de>; pgsql-sql
Thank you all for your suggestions. I will try each of these and see
which one fits my situation best.
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Andreas Gaab
Sent: Friday, November 12, 2010 12:23 AM
To: Ozer, Pam; pgsql-sql@postgresql.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@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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-11-12 16:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-11 19:07 Overlapping Ranges- Query Alternative Ozer, Pam <pozer@automotive.com>
2010-11-12 08:23 ` Andreas Gaab <A.Gaab@scanlab.de>
2010-11-12 08:57 ` Marc Mamin <M.Mamin@intershop.de>
2010-11-12 16:36 ` Ozer, Pam <pozer@automotive.com>
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