Sum of values taking into account 3 table fields

Question

I have the following table quotations that serves to fetch the totals of various budgets. And each budget has one piece (part_id) which can have several:

+---------+-------------+----------+-----------------+
| part_id | alternative | revision | totals          |
+---------+-------------+----------+-----------------+
| 1       | 1           | 0        | 252.22          |
| 2       | 0           | 0        | 452             |
| 1       | 1           | 1        | 270             |
| 1       | 2           | 2        | 250             |
| 1       | 2           | 3        | 250             |
+---------+-------------+----------------------------+

In the table, I want to sum up all the totals, but taking into account the following rules: - part_id and alterantive is like a separate budget - If there is more than one revision (revision), I’ll get the total of the last revision.

I mean, initially I need to take the following:

+---------+-------------+----------+-----------------+
| part_id | alternative | revision | totals          |
+---------+-------------+----------+-----------------+
| 2       | 0           | 0        | 452             |
| 1       | 1           | 1        | 270             |
| 1       | 2           | 3        | 250             |
+---------+-------------+----------------------------+

That is, in order to get the last revision for each alternative, I want to add the final total, which would be 972.

Answer 1

I first did the grouping to select only the largest revision and then the sum of these records:

select sum(totals) 
from quotations 
where (part_id, alternative, revision) in 
      (select part_id, alternative, max(revision) from quotations group by part_id, alternative)

Answer 2

Hello, try this on:

SELECT part_id, alternative, revision, totals
FROM (
SELECT tb1.part_id, tb1.alternative, tb1.revision, tb1.totals,
    ROW_NUMBER() OVER (PARTITION BY tb1.part_id ORDER BY tb1.revision DESC) AS RowNo
FROM orcamento tb1
) x
WHERE x.RowNo = 1

I hope I’ve helped.