How to avoid repeating row values in field hierarchy when joining over an ID

Question

I am trying to figure out a way to make rows of top level of hierarchy appear only once in SQL, directly or via an extra column:

Quantity 1 is from Table 1, Quantity 2 is from Table 2, and they are joined over the ID field. Q1 and Q2 have nothing to do with one another mathematically speaking. I would like to have the following column as a result, because I will be summing Q1 and visualizing it in parallel with the individual values of Q2.

Is there any way to do this with SQL? Or in Tableau if anyone knows a solution, as I will be plotting the data in Tableau.

Thanks in advance for your help.

Answer 1

You can use row_number() - but you need a column that defines the order of records (something that is unique across the whole table, or at least across records that have the same id). I assumed that you have such column and that it is called ordering_id:

select 
    t.*,
    case when row_number() over(partition by id order by ordering_id) = 1
        then quantity1
    end quantity1_edit
from mytable