I have 2 tables todaysracesdummy and trainerstatsdummy. I want to get the selectionid and bf_win for each eventid based on the max tcd value for each eventid. So for eventid 167019306,the selectionid would be 26986356 and the bf_win would 570 based on the tcd value of 1.07 . Hope that is clear.
CREATE TABLE [dbo].[TodaysRacesDummy](
[TODAYSRACESDummy_ID] [int] IDENTITY(1,1) NOT NULL,
[RACE_DATE] [date] NULL,
[RACE_TIME] [time](7) NULL,
[BF_WIN] [float] NULL,
[EVENT_ID] [float] NULL,
[SELECTION_ID] [float] NULL,
[Win_Pos] [int] NULL,
CONSTRAINT [PK_TodaysRacesD] PRIMARY KEY CLUSTERED
(
[TODAYSRACESDummy_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TrainerStatsdummy](
[tstatsid] [int] IDENTITY(1,1) NOT NULL,
[racedate] [date] NULL,
[eventid] [float] NULL,
[selectionid] [float] NULL,
[tcd] [float] NULL,
[winpos] [float] NULL,
CONSTRAINT [PK_tstatsdummy] PRIMARY KEY CLUSTERED
(
[tstatsid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into trainerstatsdummy
(
racedate
,eventid
,selectionid
,tcd
,winpos
)
values
('05/01/2020', 167019306, 10455028, 0, 0)
,('05/01/2020', 167019306, 19641193, 0, 0)
,('05/01/2020', 167019306, 26986356, 1.07, 0)
,('05/01/2020', 167019306, 26874788, 0.5, 1)
,('05/01/2020', 167019306, 20886044, 0.44325, 0)
,('05/01/2020', 167019306, 24590749, 1.05, 0)
,('05/01/2020', 167019311, 14239965, 0.1395, 0)
,('05/01/2020', 167019311, 11306334, 0.98, 0)
,('05/01/2020', 167019311, 11215520, 1.85, 1)
,('05/01/2020', 167019311 ,11527193, 0.99, 0)
,('05/01/2020', 167019311, 19481484, 1.36, 0)
,('05/01/2020', 167019311, 18241653, 0.94, 0)
,('05/01/2020', 167019316 ,20377664, 1.59, 1)
,('05/01/2020', 167019316 ,456718, 0.94, 0)
,('05/01/2020', 167019316 ,8262323, 1.59, 0)
,('05/01/2020', 167019316 ,9250100, 0.98, 0)
,('05/01/2020', 167019316 ,12657244, 1.02, 0)
,('05/01/2020', 167019316, 20936196, 1.02, 0)
,('05/01/2020', 167019316 ,17471568, 0.7125, 0)
,('05/01/2020', 167019316, 20347301, 1.02, 0)
,('05/01/2020', 167019316 ,20336755, 0.6655, 0)
,('05/01/2020', 167019316 ,12910941, 0, 0)
,('05/01/2020', 167019321, 23618882, 0, 0)
,('05/01/2020', 167019321, 19561216, 1.32, 0)
,('05/01/2020', 167019321 ,19721345, 0.68, 0)
,('05/01/2020', 167019321, 15768672, 0.375, 1)
,('05/01/2020', 167019321, 19044475, 0.8, 0)
,('05/01/2020', 167019321, 21078455, 0.17, 0)
,('05/01/2020', 167019321 ,11030690, 1.125, 0)
,('05/01/2020', 167019326, 11627073, 1.09, 0)
,('05/01/2020', 167019326 ,15532294, 0.83, 0)
,('05/01/2020', 167019326, 12150387, 1, 1)
,('05/01/2020', 167019326, 19381612, 0.96, 0)
,('05/01/2020', 167019326, 866292, 1.12, 0)
,('05/01/2020', 167019331, 9480388, 0.96, 0)
,('05/01/2020', 167019331, 11429585, 1.12, 0)
,('05/01/2020', 167019331, 23270563, 0.17, 0)
,('05/01/2020', 167019331 ,20969323, 1.09, 1)
,('05/01/2020', 167019331, 19679364, 0.83, 0)
,('05/01/2020', 167019331 ,10559117, 0.57, 0)
,('05/01/2020', 167019301, 10108396, 0.43, 0)
,('05/01/2020', 167019301 ,18484281, 0.93, 0)
,('05/01/2020', 167019301 ,14971907, 0.775, 0)
,('05/01/2020', 167019301 ,21179785, 1.09, 0)
,('05/01/2020', 167019301 ,19186442, 1.39, 0)
,('05/01/2020', 167019301, 12146123, 0.95, 1)
,('05/01/2020', 167019301, 13202265, 0.68, 0)
insert into todaysracesdummy
(
RACE_DATE
,RACE_TIME
,BF_WIN
,EVENT_ID
,SELECTION_ID
,Win_Pos
)
values
(
'05/01/2020', '01:00:00', 26, 167019306, 10455028, 0),
('05/01/2020', '01:00:00', 5.7, 167019306, 19641193, 0)
,('05/01/2020', '01:00:00', 570, 167019306, 26986356, 0)
,('05/01/2020', '01:00:00', 2.02, 167019306, 26874788, 1)
,('05/01/2020', '01:00:00', 3.5, 167019306, 20886044, 0)
,('05/01/2020', '01:00:00', 85, 167019306, 24590749 ,0)
,('05/01/2020', '01:30:00', 19.5, 167019311, 14239965, 0)
,('05/01/2020', '01:30:00', 10, 167019311, 11306334 ,0)
,('05/01/2020', '01:30:00', 4.6, 167019311, 11215520, 1)
,('05/01/2020', '01:30:00', 6, 167019311 ,11527193 ,0)
,('05/01/2020', '01:30:00', 2.84, 167019311, 19481484, 0)
,('05/01/2020', '01:30:00', 7.8, 167019311, 18241653, 0)
,('05/01/2020', '02:00:00', 14, 167019316 ,20377664 ,1)
,('05/01/2020', '02:00:00', 14, 167019316 ,456718 ,0)
,('05/01/2020', '02:00:00', 60, 167019316 ,8262323, 0)
,('05/01/2020', '02:00:00', 32, 167019316 ,9250100, 0)
,('05/01/2020', '02:00:00', 5, 167019316 ,12657244, 0)
,('05/01/2020', '02:00:00', 19.5, 167019316, 20936196, 0)
,('05/01/2020', '02:00:00', 32, 167019316 ,17471568 ,0)
,('05/01/2020', '02:00:00', 3.8, 167019316, 20347301, 0)
,('05/01/2020', '02:00:00', 4, 167019316 ,20336755 ,0)
,('05/01/2020', '02:00:00', 30, 167019316 ,12910941 ,0)
,('05/01/2020', '02:30:00', 6.8, 167019321, 23618882, 0)
,('05/01/2020', '02:30:00', 7.6, 167019321, 19561216, 0)
,('05/01/2020', '02:30:00', 36, 167019321 ,19721345 ,0)
,('05/01/2020', '02:30:00', 5.8, 167019321, 15768672, 1)
,('05/01/2020', '02:30:00', 5.4, 167019321, 19044475, 0)
,('05/01/2020', '02:30:00', 3.65, 167019321, 21078455, 0)
,('05/01/2020', '02:30:00', 13, 167019321 ,11030690 ,0)
,('05/01/2020', '03:00:00', 4.1, 167019326, 11627073, 0)
,('05/01/2020', '03:00:00', 6.8 ,167019326 ,15532294 ,0)
,('05/01/2020', '03:00:00', 3.15, 167019326, 12150387, 1)
,('05/01/2020', '03:00:00', 10.5, 167019326, 19381612, 0)
,('05/01/2020', '03:00:00', 4.7, 167019326, 866292 ,0)
,('05/01/2020', '03:30:00', 4.8, 167019331, 9480388 ,0)
,('05/01/2020', '03:30:00', 16, 167019331, 11429585 ,0)
,('05/01/2020', '03:30:00', 3.3, 167019331, 23270563, 0)
,('05/01/2020', '03:30:00', 4, 167019331 ,20969323 ,1)
,('05/01/2020', '03:30:00', 7.4, 167019331, 19679364, 0)
,('05/01/2020', '03:30:00', 19, 167019331 ,10559117 ,0)
,('05/01/2020', '12:30:00', 9.4, 167019301, 10108396, 0)
,('05/01/2020', '12:30:00', 30, 167019301 ,18484281 ,0)
,('05/01/2020', '12:30:00', 11, 167019301 ,14971907 ,0)
,('05/01/2020', '12:30:00', 12, 167019301 ,21179785 ,0)
,('05/01/2020', '12:30:00', 13, 167019301 ,19186442 ,0)
,('05/01/2020', '12:30:00', 2.24, 167019301, 12146123, 1)
,('05/01/2020', '12:30:00', 5.7, 167019301, 13202265, 0)