Anyone has a better idea to tackle this problem.

I have this table

```
+------+------+
|Id    |Value |
+------+------+
|1     |0     |
+------+------+
|1     |5     |
+------+------+
|2     |0     |
+------+------+
|2     |1     |
+------+------+
|3     |0     |
+------+------+

```

So my goal is to get the distinct Id and get the first non-zero value if it exist.

Which would look like this

```
+------+------+
|Id    |Value |
+------+------+
|1     |5     |
+------+------+
|2     |1     |
+------+------+
|3     |0     |
+------+------+

```

One of my idea is to SUM the Value since the expectation the rest are 0 but that won't work because their would be a possibility of having more than 1 value.

In this case I don't care which value I get as long as I got only one non-zero.

Is this possible in SQL or should I do this in backend?

MySQL Version: 5.7.26
['https://Stackoverflow.com/questions/62542223', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/4099041/']
If you are running MySQL 8.0, you can use `row_number()` - but you need an ordering column, otherwise the notion of "first" value is not defined. I assumed `ordering_id`:

```
select id, value
from (
    select
        t.*,
        row_number() over(
            partition by id 
            order by (value = 0), ordering_id
        ) rn
    from mytable t
) t
where rn = 1

```

In earlier versions, one option is to use a subquery. Assuming that the primary key of your table is `pk`, you would do:

```
select t.id, t.value
from mytable t
where t.pk = (
    select t1.pk
    from mytable t1
    where t1.id = t.id
    order by (value = 0), ordering_id
    limit 1
)

```
This solution work for my need: [How can I select only the first distinct match from a field in MySQL?](https://stackoverflow.com/questions/15010763/how-can-i-select-only-the-first-distinct-match-from-a-field-in-mysql)

Instead of using MIN, I used MAX so it will get the biggest value (In my scenario this is ok since I don't care which non-zero I get as long it exist)

Cheers