Selecting one arbitrarily chosen row from each group

[Ralf]

Hi,

i try to find a query for selecting one arbitrarily chosen row from
each group of rows.

Example:

$ db2 "select * from T"

A B C
----------- ----------- -----------
100 1 10
100 20 2
101 3 3

3 record(s) selected.

The result should be 'Select A From T group by A' but with one (B,C)-
pair for each group.

So {(100, 1, 10), (101, 3, 3)} and {(100, 20, 2), (101, 3, 3)} would
both be valid results,
but not {(100, 1, 2), (101, 3, 3)}.

Is there a way to formulate this in SQL?

Thanks,
Ralf

[Knut Stolze]

Ralf wrote:

Hi,

i try to find a query for selecting one arbitrarily chosen row from
each group of rows.

Example:

$ db2 "select * from T"

A B C
----------- ----------- -----------
100 1 10
100 20 2
101 3 3

3 record(s) selected.

The result should be 'Select A From T group by A' but with one (B,C)-
pair for each group.

So {(100, 1, 10), (101, 3, 3)} and {(100, 20, 2), (101, 3, 3)} would
both be valid results,
but not {(100, 1, 2), (101, 3, 3)}.

Is there a way to formulate this in SQL?

SELECT x.a, x.b, x.c
FROM ( SELECT t.*, ROW_NUMBER() OVER(PARTITION BY t.a) AS rn
FROM yourTable AS t ) AS x
WHERE x.rn = 1

A B C
----------- ----------- -----------
100 1 10
101 3 3

2 record(s) selected.
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany