How to use condition in column in group by with agg request

it's a bit hard to explain, i dont know how to use a condition in a column-select attribute in a SQL who have group by and aggregat functions...

Session	DateStart	DateEnd	TradeID	TradeKP
1	2024-12-09 11:20:15.000	2024-12-09 11:20:16.000	tr1	ICM
1	2024-12-09 11:20:07.000	2024-12-09 11:20:48.000	tr53	ICT
1	2024-12-09 11:22:07.000	2024-12-09 11:24:48.000	.	ICM
2	2024-12-08 04:55:09.000	2024-12-08 04:55:11.000	jik67	SMC
2	2024-12-08 04:55:13.000	2024-12-08 04:55:38.000	.	PLM

I want to return grouping with session :

The earliest Startdate (Using MIN aggregat, is ok it work)
The most recent EndDate (Using MAX aggregat, is ok it work)
The TradeKP which corresponds on the line who have "." in TradeID. (It's the problem how to do that?) For information, one session can have 1 to 600 lines. One session only have one line with "." in TradeID The line who have "." maybe anywhere and therefore has no link with the date. The number total of row of this table can reach up to 500 millions of lines.

Exemple :

Session	Beginning	Ending	TradingMaster
1	2024-12-09 11:20:07.000	2024-12-09 11:24:48.000	ICM
2	2024-12-08 04:55:09.000	2024-12-08 04:55:38.000	PLM

I tried with this request, but it doesn't work :

SELECT session,
       MIN(DateStart) as beginning,
       MAX(DateEnd) as ending,
       MAX(tradeKP) as TradingMaster
FROM SCHEMA.TRADES
GROUP BY session

Solution

You can take value of column tradeID only for rows with condtition tradeID='.' ignoring (null) all other's.

Try

SELECT session,
       MIN(DateStart) as beginning,
       MAX(DateEnd) as ending,
       MAX(case when tradeID='.' then tradeKP end) as TradingMaster
FROM SCHEMA.TRADES
GROUP BY session