Get value from 1 table based on max value from another table

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)

i hope this is what you are looking for !!! :slight_smile:

please click arrow to the left for SQL
; with cte as 
 (
 select 
	  a.EVENT_ID
	, max(b.tcd) as maxtcd
	, max(a.bf_win) as maxbf
 from 
       [TodaysRacesDummy] a 
           join 
	   [TrainerStatsdummy] b 
              on 
			    a.SELECTION_ID = b.selectionid
				 AND 
				  a.event_id = b.eventid
 group by 
   a.EVENT_ID
) 
select 
   *
from 
  cte

image

That is a great reply thank you, but I also need the selectionid that corresponds to the max tcd if that is possilbe.

hi i hope this is what !!!

please click arrow to the left for SQL .. new
; with cte as 
     (
     select 
          a.EVENT_ID
    	, max(b.tcd) as maxtcd
    	, max(a.bf_win) as maxbf
     from 
           [TodaysRacesDummy] a 
               join 
    	   [TrainerStatsdummy] b 
                  on 
    			    a.SELECTION_ID = b.selectionid
    				 AND 
    				  a.event_id = b.eventid
     group by 
       a.EVENT_ID 
    ) 
    select 
        'SQL OutPut'
      , b.SELECTION_ID
      , a.*
    from 
      cte a 
       join  
         [TodaysRacesDummy] b
    	  on 
    	    a.maxbf = b.BF_WIN

image v

1 Like

Blimey, that's exactly it! Many many thanks.