# SQL, operation between paired rows

I have a table t1

```a   b
1   4
2   2
3   9
2   1
1   6
3   7
```

I need the output

```a   result
1   4*6
2   2*1
3   9*7
```

I have been able to do it in a far from efficient way and would like a more satisfactory way. Thank you.

If you don’t want a floating point result, and you can have more than 2 `b` values for each `a` value, you can use a recursive CTE to generate the results:

```WITH RECURSIVE t1r AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY a) AS rn
FROM t1
),
maxrows AS (
SELECT a, MAX(rn) AS maxrow
FROM t1r
GROUP BY a
),
mul AS (
SELECT a, b AS result, rn, rn AS maxrow
FROM t1r
WHERE rn = 1
UNION ALL
SELECT mul.a, result * b, t1r.rn, t1r.rn
FROM mul
JOIN t1r ON t1r.a = mul.a AND t1r.rn = mul.rn + 1
)
SELECT mul.a, mul.result
FROM mul
JOIN maxrows mr ON mr.a = mul.a AND mr.maxrow = mul.rn
ORDER BY mul.a
```

Output:

```a   result
1   24
2   2
3   63
```

Demo on dbfiddle

Note that this could be simplified if there was a column by which you could order the rows, that would remove the need to have the `maxrows` CTE.

Source: stackoverflow