Selecting one arbitrarily chosen row from each group

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

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