CODEWITHSUNDEEP
sqlpostgresqlpostgresql-8.4
Edson Horacio Junior
Edson Horacio Junior

Reputation: 3143

select a "fake" column that is a range of specific numbers

I need to select table's a information along with a range of specific numbers, that is, for every a.id I want to show every number in the range, using PostgreSQL 8.4.

Let's suppose the range is the numbers 123, 175 and 192, this would be the result I want:

rangea.id
123   1    
175   1    
192   1    
123   2    
175   2    
192   2    
123   3    
175   3    
192   3    

I know I can achieve this using

select range, a.id
from a
inner join generate_series(1, 100, 1) range on true

But the thing is, I don't want to use generate_series because my range is random numbers, is there another way to do it?

Maybe something like this:

select range, a.id
from a
where range in (123, 175, 192)
group by range, a.id;

Upvotes: 3

Views: 865

Answers (3)

Edson Horacio Junior
Edson Horacio Junior

Reputation: 3143

@sgeddes and @klin answers have contributed a lot, even though I have accepted a answer, I'm writing this one to describe 3 solutions that I found to work and are "elegant". Sadly, I don't know which performs better.

// This is the one I'm using
select unnest(array[123, 175, 192]), a.id
from a
group by range, a.id

select range, a.id
from a
inner join (values (123), (175), (192)) data(range) on true
group by range, a.id

select range, a.id
from a
inner join unnest(array[123, 175, 192]) range on true
group by range, a.id

Upvotes: 1

klin
klin

Reputation: 121514

I understand you want to have a query you can easily use with a list of multiple numbers as parameter. Use an array:

with a(id) as (values (1), (2), (3))

select rng, id
from unnest(array[123, 175, 192]) rng
cross join a;

 rng | id 
-----+----
 123 |  1
 175 |  1
 192 |  1
 123 |  2
 175 |  2
 192 |  2
 123 |  3
 175 |  3
 192 |  3
(9 rows)

Upvotes: 2

sgeddes
sgeddes

Reputation: 62831

Given your comment:

For every a.id I want to show every number of the range

This creates what is called a cartesian product. Here's one generic option using a cross join with union all:

select a.id, r.rng
from a cross join (
  select 123 as rng 
  union all select 234
  union all select 556
  union all select 653
  union all select 634) r

Upvotes: 3

Related Questions