0

using postgresql v8.2

For example I have such a table:

 id  | va
-----+-----
 123 | 234
 123 | 234
 123 | 456
 234 | 123
 234 | 345
 456 | 678
(6 rows)

what I want is to query from the table to get randomly anyone with the same ID

it may like:

 id  | va
-----+-----
 123 | 234
 234 | 123
 456 | 678
(3 rows)

or like:

 id  | va
-----+-----
 123 | 456
 234 | 345
 456 | 678
(3 rows)

which value doesn't matter, but there have to be one. Thanks for proposing me any methods.

cinqS
  • 1,175
  • 4
  • 12
  • 32
  • You should plan to a supported and maintained version **now** (e.g. 9.5). 8.2 is long dead and forgotten –  May 31 '16 at 10:37
  • by the way, I am using `Greenplum`, which is based on `postgresql v8.2`, and this is a legacy version. – cinqS May 31 '16 at 12:16

2 Answers2

1

select id, max(va) from table group by id;

As you state that value of va doesn't matter. You could select any aggregate.

You can also use distinct on

select distinct on (id) id, va from table order by id, random();

Mukul Gupta
  • 2,310
  • 3
  • 24
  • 39
  • can you explain me what the `random()` here be used for? I usually used it for generating double precision values. thanks – cinqS May 31 '16 at 12:19
  • Yes, `random()` generates a double precision value. Here, think of it as an extra column filled with random double values which are sorted on the extra column. The advantage of using `distinct on` is that the `va` field is random and not necessarily a `max`, `min`, etc. – Mukul Gupta May 31 '16 at 12:34
  • ah, ok, an extra column can be used to sort the query result, and here you chose random value. this enable the random choice of the value. I got it, thanks! – cinqS May 31 '16 at 13:23
0

Are you looking for GROUP BY ? It will just select one row for each Id in your table.

Postgresql group by

The PostgreSQL GROUP BY clause is used in collaboration with the SELECT statement to group together those rows in a table that have identical data.

Is your case you could have the following query : 

SELECT id, max(va) FROM tablename GROUP BY id ;
Gauthier
  • 1,116
  • 2
  • 16
  • 39