Define Position based on lowest ID and Foreign Key in MySQL

Question

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 ;)

score 0 · Accepted Answer · answered Nov 01 '20 at 21:23

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.

Thank you this really solved my issue. I am relatively new to SQL so I was not aware that something like this is even possible :) — , Nov 01 '20 at 21:34

Define Position based on lowest ID and Foreign Key in MySQL

1 Answers1