Define Position based on lowest ID and Foreign Key in MySQL

I am currently facing the following issue: I have 1 Table with my Broker Trading Data similar to this:

TickerId      Id   Ticker   Shares  OrderType
...           ...  ...      ...     ...
01.01.20 ABC  5    ABC      500     Buy
01.01.20 ABC  6    ABC      250     Sell
01.01.20 ABC  7    ABC      250     Sell
...           ...  ...      ...     ...

The Goal is to say IF first OrderType (lowest Id where TradeId is the same) was a Buy, it’s a LONG Trade ELSE a Short Trade … output should be like this:

TickerId       Position   Ticker   Volume (=SUM(Shares))
...            ...        ...      ...
01.01.20 ABC   Long       ABC      1000
...            ...        ...      ...

What am I missing? How can I construct my Query to accomplish this task?

Thanks for looking into this 😉

Answer

If you want to add this to all rows, use a window function. One method is:

select t.*,
       (case when first_value(orderType) over (partition by tickerid order by id) = 'Buy'
             then 'Long' else 'Short'
        end) as position
from t;

If you just want one row per tickerid, you can use aggregation:

select tickerid,
       (case when min(case when orderType = 'Buy' then id end) = min(id)
             then 'Long' else 'Short'
        end) as position
from t
group by tickerid;

The logic here is that the first “Buy” id is compared to the first “id”. If they are the same, you have a “Long” trade.