Vadim Shkaberda - 1 year ago 72

SQL Question

The question based on SQL query to select distinct row with minimum value.

Consider the table:

`id game point`

1 x 1

1 y 10

1 z 1

2 x 2

2 y 5

2 z 8

Using suggested answers from mentioned question (select the ids that have the minimum value in the point column, grouped by game) we obtain

`id game point`

1 x 1

1 z 1

2 x 2

The question is how to obtain answer with single output for each ID. Both outputs

`id game point`

1 x 1

2 x 2

and

`id game point`

1 z 1

2 x 2

are acceptable.

Answer Source

Use `row_number()`

:

```
select t.*
from (select t.*,
row_number() over (partition by id order by point asc) as seqnum
from t
) t
where seqnum = 1;
```