MySQL Get only first match of a conditions between 3 columns

Question

I am working with an API REST (NodeJS) that have access to a MySQL DB. In this database, there is a table that looks like this (let's name it Table_01):

| C_0| C_1| C_2| C_3| 
|  1 | A1 | B1 |  1 |
|  2 | A1 | B2 |  0 |
|  3 | B1 | A1 |  0 |
|  4 | A2 | B1 |  0 |
|  5 | B2 | A1 |  1 |
|  6 | B1 | A1 |  0 |
|  7 | B3 | A1 |  0 |
|  8 | A1 | B3 |  1 |
|  9 | A3 | B1 |  0 |
| 10 | A1 | B3 |  1 |
| 11 | A1 | B1 |  0 |

My objective is to get the first match of a Value = 1 on C_3 for a pair of values in C_1 and C_2.

Let's put an example.

I want to know in what cases A_1 in C_1, have a 1 in C_3 (only the first match)

So, in this case, the result should be:

A1 B1 1
A1 B3 1

In JSON format:

result = [
  {
    "C_1": "A1",
    "C_2": "B1",
    "C_3": "1",
  },
  {
    "C_1": "A1",
    "C_2": "B3",
    "C_3": "1",
  }
];

I don't want to receive TWO times the second one, since A1 B3 = 1 is included 2 times in the table.

I can get the result doing this:

db.query('SELECT * FROM `Table_01` WHERE (`C_1` = A1 AND `C_2` = '+db.escape(value)+') AND `C_3` = 1',
  (err, res) => {
    ..
  }
);

But this would give me a third entry with the duplicated A1 B3 1.

Obviously in this example is not very important.. But with a table full of rows, this is important to avoid very large responses and a posterior JS filter.

Answer 1

One approach uses a correlated subquery for filtering:

select t.*
from table_01 t
where t.id = (
    select min(t1.id) 
    from table_01 t1 
    where t1.c_1 = t.c_1 and t1.c_2 = t.c_2 and t1.c_3 = 1 
)

You can also use window functions, if you are running MySQL 8.0:

select *
from (
    select t.*,
        row_number() over(partition by c_1, c_2 order by id) rn
    from mytable t
    where c_3 = 1
) t
where rn = 1