Need help resolving a SET based Query where I have hardcoded values
-
I have a DB full of RACES and RUNNERS, COURSES, STATS etc etc. One horse could appear in the table dozens of times and usually I don't have issues as I load in the races and use a RacePK ID to get all the runners for a single race out and then order them by own rating score.
However as it's Christmas and there is no racing on until the big Boxing Day Kempton races I want to get all the runners in that race and see from their last race what their ratings and KPI's are, to form an early opinion to take with me, as I won't have my laptop where I am staying for XMAS.
To simplify the code for testing I have just mocked up a temp table variable with a few columns and then inserted multiple rows for each horse with different dates and ratings.
So at the moment I have this...
DECLARE @RACES TABLE(RunnerPK INT IDENTITY(1,1),HorseName nvarchar(100),FinishPosition INT,RaceDate Date,Rating INT)
INSERT INTO @RACES
(HorseName,FinishPosition,RaceDate,Rating)
VALUES
('Frodon',1,'2021-OCT-01',180),
('Frodon',3,'2021-JUN-14',132),
('Frodon',2,'2021-JAN-22',145),
('Dashel Drasher',1,'2021-JUN-14',163),
('Dashel Drasher',2,'2021-DEC-02',160),
('Lostintranslation',1,'2021-DEC-02',174),
('Lostintranslation',1,'2021-JAN-22',164),
('Lostintranslation',1,'2021-JUN-06',170),
('Asterion Forlonge',3,'2021-DEC-02',159),
('Asterion Forlonge',2,'2021-JUN-14',165),
('Asterion Forlonge',1,'2021-NOV-21',172),
('Minella Indo',3,'2021-OCT-01',178)
SELECT HorseName,FinishPosition,LEFT(RaceDate,11),Rating
FROM @RACES
WHERE HorseName IN('Frodon','Dashel Drasher','Lostintranslation','Asterion Forlonge','Minella Indo')
ORDER BY Racedate DESC,Rating DESCHowever the result comes out like this...
HorseName FinishPos RaceDate Rating
Lostintranslation 1 2021-12-02 174
Dashel Drasher 2 2021-12-02 160
Asterion Forlonge 3 2021-12-02 159
Asterion Forlonge 1 2021-11-21 172
Frodon 1 2021-10-01 180
Minella Indo 3 2021-10-01 178
Asterion Forlonge 2 2021-06-14 165
Dashel Drasher 1 2021-06-14 163
Frodon 3 2021-06-14 132
Lostintranslation 1 2021-06-06 170
Lostintranslation 1 2021-01-22 164
Frodon 2 2021-01-22 145And what I need it this without all the duplicates, e.g only the latest occurrence of each horse racing...
HorseName Pos RaceDate Rating
Frodon 1 Oct 1 2021 180
Minella Indo 3 Oct 1 2021 178
Lostintranslation 1 Dec 2 2021 174
Dashel Drasher 2 Dec 2 2021 160
Asterion Forlonge 3 Dec 2 2021 159So I am trying to get the last race for each runner, whatever the date, and then rank them by the Ratings they had on that day to help me predict this race in the future.
As they appear multiple times in my DB/RACES table for numerous dates and races I am having to hardcode the horse names into an IN statement but I obviously don't want duplicates.
I could do numerous WHERE OR clauses where I get the TOP(1) HorseName FROM RACES WHERE HorseName=X ORDER BY Racedate DESC for each runner OR put them all in a temp table and use a loop to get the latest race out per horse but I am sure there is a better SET based way to do this and it's been a while.
I've just got out of hospital for some flesh eating bug that was sending me mad apparently, had half my thigh cut out, and I blacked out head butting the sink causing a nice mars bar on my forehead, which got me rushed into AnE for head scans before the op, so my brain is not in an optimal state at the moment.
I am sure there is a better way to do it without using multiple WHERE clauses OR temp tables/views to insert the top 1 for each Horse by RaceDate 1st before ordering them by rating. I am using SQL 2017 if that matters by the way.
Thanks for any help in advance, I can obviously just insert the PKS by hand for now, but I would like to know the optimal way to do this query.
- This topic was modified 2 years, 11 months ago by Rob Reid-246754. Reason: Thought I could sort out the formatting of the results as it was all bunched together for some reason
-
I have a DB full of RACES and RUNNERS, COURSES, STATS etc etc. One horse could appear in the table dozens of times
I think you may be missing a table. How are Races and Runners related? What's the structure of Runners? I would think you would need something like
Horse--(1,M)-- Runs in --(M,1)--Race--(M,1)--Course
Each horse can run in one or more races. Each race is between one or more Horses on a single course. Right?
-
Yes I do I have a RACES table (RacePK, Racedate, Time, CourseFk, NoOfRunners, RaceType and X more), Courses table (CoursePK, CourseName, Country) and RACE_RUNNERS(RunnerPK,HorseName,Jockey,Trainer,Form,FormRating1-5,Speed Rating,OR,hasMaxOR....tons more).... I just tried to make it simple for anyone trying to replicate the process with a simple table variable with just a couple of key columns in otherwise it would have taken ages and too long to output all the tables, keys, why I have column x y and z etc when in reality it doesn't matter it's just a case of getting the latest record from a dataset for each unique item (horse name in the example) and outputting only one record for each. I can then transfer that to my Database and my query.
I thought I would just make it simple for anyone trying to help me by having one table but of course in my DB there are plenty more tables as I said in my first sentence, and then in the 3rd sentence to keep it simple ..... so I hope you understand.
Thanks for helping, that is if you do 🙂
-
And also to your last point..
Each horse can run in one or more races. Each race is between one or more Horses on a single course. Right?
Yes of course, but this race is in the future, and the horses have raced in many races previously. If it was the day of the race I would have inserted all of those runners (King George VI - Boxing Day - Kempton) into the appropriate tables and then I would have just got all the data out with the RacePK.
As I cannot import the data yet (some of it isn't even defined like odds, jockeys etc), I just want to hardcode the runners that are listed for now, get their last races out of the DB for each runner and then look at their last scores, ratings, form, form on course etc. I don't know the going (ground conditions either), so it is a preview using old data rather than up to date on the day data with current market prices, ground conditions etc etc.
Hope you understand what I am trying to do as this is antepost.
-
I would just create the proper tables (not munging all the data into a single table) and then put in fake data to test it. So maybe 3-4 records in the biggest table. All you're trying to do at that point is to verify that the queries work. If you're writing what should be a simple query and it's hard to write, then there's a very good chance that your design is wrong. And there's no point in loading a ton of data into a bad design. So test first, then truncate all the tables, and reload with real data.
-
If you win, send me 5%... 😀
The following will do the trick for you according to your original post. Thanks for the readily consumable data.
WITH cteEnumerate AS
(
SELECT *
,FinalOccur = ROW_NUMBER() OVER (PARTITION BY HorseName ORDER BY RaceDate DESC)
FROM @RACES
WHERE HorseName IN('Frodon','Dashel Drasher','Lostintranslation','Asterion Forlonge','Minella Indo')
)
SELECT HorseName,FinishPosition,RaceDate,Rating
FROM cteEnumerate
WHERE FinalOccur = 1
ORDER BY RaceDate, Rating DESC
;Results:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
with recent_cte(HorseName, FinishPosition, RaceDate, Rating) as (
SELECT top 1 with ties HorseName, FinishPosition, RaceDate, Rating
FROM @RACES
order by row_number() over (partition by HorseName order by RaceDate desc))
select HorseName, FinishPosition, RaceDate, Rating
from recent_cte
order by Rating DESC;Or equivalently
with recent_cte(HorseName, FinishPosition, RaceDate, Rating, rn) as (
SELECT HorseName, FinishPosition, RaceDate, Rating,
row_number() over (partition by HorseName order by RaceDate desc)
FROM @RACES)
select HorseName, FinishPosition, RaceDate, Rating
from recent_cte
where rn=1
order by Rating DESC;Since the goal is to make the information browsable... maybe make use of the ordering by 'RaceDate' to create a prior history line summary by taking LAG(s) and using STRING_AGG to concatenate the remaining race history.
with recent_cte(HorseName, FinishPosition, RaceDate, Rating, rn, lag_rating, lag_dt, lag_fp) as (
SELECT HorseName, FinishPosition, RaceDate, Rating,
row_number() over (partition by HorseName order by RaceDate desc),
lag(Rating) over (partition by HorseName order by RaceDate),
lag(RaceDate) over (partition by HorseName order by RaceDate),
lag(FinishPosition) over (partition by HorseName order by RaceDate)
FROM @RACES)
select HorseName, FinishPosition, RaceDate, Rating, isnull(str_agg.prior_history, 'None') prior_history
from recent_cte r
outer apply (select string_agg(concat_ws('-', quotename(datediff(day, lag_dt, RaceDate), '()'),
concat('p', lag_fp),
concat('r', lag_rating)), '||')
within group (order by RaceDate desc)
from recent_cte rr
where rr.HorseName=r.HorseName
and rr.rn<4 /* only look at most recent 3 results */ and rr.lag_dt is not null) str_agg(prior_history)
where rn=1
order by Rating DESC;Output
HorseName FinishPosition RaceDate Rating prior_history
Frodon 1 2021-10-01 180 (109)-p3-r132||(143)-p2-r145
Minella Indo 3 2021-10-01 178 None
Lostintranslation 1 2021-12-02 174 (179)-p1-r170||(135)-p1-r164
Dashel Drasher 2 2021-12-02 160 (171)-p1-r163
Asterion Forlonge 3 2021-12-02 159 (11)-p1-r172||(160)-p2-r165Since the order of the 'prior_history' string (within row grouping) is based on the RaceDate descending it could be read like "On Ocotober 1st 2021 with a rating of 180, Frodon came in 1st place. 109+1 days previously Frodon came in 3rd place with a rating of 132. 143+1 days previously Frodon came in 2nd place with a rating of 145". The date differences are incremental so the 3rd race was 109+143+2 days prior to October 1st
[Edit] The added +1's are so the results could be matched up to a calendar. Otherwise, the date differences (without +1's) are "boundary crossings"
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Sorry but I don't understand why you need all the huge code of a real tables and indexes and data which would take ages to fill out. Just think of it as a generic problem not horse racing, where you just have one table with multiple records for each item on different dates that you need to get the latest record by date for each instance and ony display that, ignoring all records (horses), with older dates.
I have tried before to copy all my tables and info into this SQL Editor and it always says it's too much to post plus it would take me ages by hand to fill out test data for 4+ tables in a join due to all the ratings and scores etc.
Hope you can just think of it as a generic task and not worry about the real data as it is just the way to do it like the 2 people below have.
Thanks though for looking at it.
-
Thanks Jeff,
I was hoping you might come along if I mentioned SET BASED in the Title/Tags 🙂
That's exactly what I needed, not too much editing for me, and a clear and simple CTE for that one table. I can just plop my actually huge query joining all the 4+ tables, UDFS etc into the middle of that CTE and it works perfectly.....ching ching... if I get the bet right I'll split it with you, but I think I remember from an older post about Football Accumulators being split and randomized into multiple groups (accumulators), that you said you were "against gambling" or something along those sorts so surely any winnings would horrify you no? Then again I might have brain damage after my fall against the sink so my memory is pretty bad at the moment, LOL
Thanks for working it out though, I really do appreciate it especially as I can take this simple example and just plop my existing record set into the middle.
Thanks all of you and have a good XMAS!!
-
Hi Steve,
Sorry your way works, but compared to Jeffs it's a bit more work and I have the record set ready to plop it in the middle of a CTE. I was sure you could but just couldn't remember how.
Also I already have code that gives me their last race info in the same record set as the current race, race type, class, going, last finish position etc; as I store the last racePK as a raceFK in the RACE_RUNNERS table so I can always reference the runners last race and get either a single value, or call a UDF if I need formatted info such as the horses in the current race I was in front of in the last race etc; or the ground conditions or my KPIS [CDGJSL] (Course, Distance, Going, Jockey, Surface, Level - Class) for both WON and PLACED (1-[3-6]) etc over the current race conditions.
However thank you for going to so much effort to rewrite it with all those changes. It would have taken me ages at the moment to surf the web and work it out from examples on various sites.
It is so nice to be part of SQLServerCentral Community where you can just ask a question and as long as you post the code to help people replicate the issue you don't get any snarky replies and usually people help you if they can.
Have a good one!!
- Rob Reid-246754 wrote:
Thanks Jeff,
I was hoping you might come along if I mentioned SET BASED in the Title/Tags 🙂
That's exactly what I needed, not too much editing for me, and a clear and simple CTE for that one table. I can just plop my actually huge query joining all the 4+ tables, UDFS etc into the middle of that CTE and it works perfectly.....ching ching... if I get the bet right I'll split it with you, but I think I remember from an older post about Football Accumulators being split and randomized into multiple groups (accumulators), that you said you were "against gambling" or something along those sorts so surely any winnings would horrify you no? Then again I might have brain damage after my fall against the sink so my memory is pretty bad at the moment, LOL
Thanks for working it out though, I really do appreciate it especially as I can take this simple example and just plop my existing record set into the middle.
Thanks all of you and have a good XMAS!!
Heh... old age does change some things. Think of it as a donation to my retirement fund.
One of my big problems before was that I didn't understand that you weren't one of the people that was taking advantage of people with a gambling problem and seeing what you posted here also says to me that YOU don't have a gambling problem. In other words, no one is going hungry or going to lose their house because of you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes, I do not work for any bookmaker, this is code for myself and my Dad who recently got into betting on the horses after winning the Grand National last year, he won about £70 from a small wager and he has been betting for fun, all year with just the profit from that single bet, he only places a max of 3 bets a day, doesn't chase loses and doesn't constantly keep depositing money into his account. He still has money left from that bet last year. It has gone up and down and up and down but it's just for fun and gives us two something to actually talk about together that won't cause an argument which is good!
I think if you are betting for fun or as I do, I have conditioned myself from the days of working behind a desk and throwing a few hundred quid on a horse to only putting £5 in the bank every 2 weeks and I try to turn £1 into £100 every week, it's a good skill to have, as you are trying to maximise profits from small stakes AKA those football accas at 10p an acca (max stake £2 = 20 bets), and I have won 20 folds and missed out on a 25 fold on the very last game, Man City v Leicester I think last season, it would have won me over £3,000 from £0.10 stake.... gutted but as I say if you can train yourself to see it as a way of making money and put all the stop at £x profit (if made) per day, or a % of your bank, not be greedy, not chase losses and not throw away money it can be a good way to make some and just have some fun.
I actually won the ARC d'Triomphe this year betting on the outsider of the race, a German multiple Grade winning horse Torquator Tasso, that was priced at 80/1. I only put a few quid on it and it came first, German Horses, are often overlooked, especially in the Arc.
I won a few years ago on another German Horse Waldgeist on my birthday in 2019 @ 16/1 after the previous year winning more on that same horse coming 4th. The price for it to just place (come in top 2-6 in UK), was more than betting on the eventual winner which I also had, Enable @ 10/11.
So that is a tip for you all when the Arc comes along look for long priced, German horses as they are rarely favourites and many are long priced like this years 80/1 winner.
However I get what you mean, and it does take immense practise and self control to say "I will not bet anymore today after losing X bets", there is always another day.
However when I actually make money from it I spend that money on things I need like Rent, Food, A nice 4k Smart TV and a holiday to Iceland when I won a Treble at the US Breeders Cup for £8k. So yes if I was working for the sites like Bet365 or Sky who have both banned me for life for winning (that's what happens, so if you ever see some scam tipster offering $$ for a fee, showing massive winnings on those 2 sites you can be rest assured it is a photoshop job of his account), the only sites that won't kick you off are the EXCHANGES, Betfair/BetDaq, as there is NO bookmaker and you bet against other bettors at any price they put up for the horse to win or lose.
It's a game the bookies always win, but if you can get a nice win now and then along the way and have the sense to take that money out and spend it on something nice or that you need, then you are not a gambler you are a bettor.
I can look all around my room and see things I have bought from winnings from Pictures on the wall to bronze horse statues etc. It is nice to win and spend that money because you, cannot and, will not win every time, and that is why you need to have some self control and not everyone can manage it.....just like my Scottish Dad on the Single Malt at XMAS, some things should be kept apart 🙂
Anyway thanks for the code it was just what I needed. I just hope Chantry House @ 9/2 and Frodon at 6/1 both WIN (DUTCH) and PLACE EVNS (3 spots) @ 11/10 (4 spots) in the King George VI at 15:05.
Always Epatante in the 14:30 and Bravemansgame in the 13:55, add to WIN/PLACE accas and singles but don't blame me if they lose!
I have lost many a race at Cheltenham when for some reason the jockey and horse are out at front and then fall at the last wooden fence, cough, Annie Power who saved the bookies millions in accas.
Anyway enjoy XMAS and thanks for the code!
-
Glad it helped. You helped a lot by posting readily consumable data. Makes our "job" a whole lot easier and enjoyable.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi,
Yes I didn't really understand why someone would need the real tables and joins and extra stuff to replicate a problem that could be described easily with one temp table to work on.
If I had of tried copying table definitions etc I don't think the site would have let me upload it all.
Thanks for the help, lets see how many of these horses win/lose on Boxing day now....
Time to wrap my gifts. I hate XMAS wrapping, if 2 people set up a stall in a shopping centre each year and charged people to wrap their gifts I swear they would make a mint. I know I and most other blokes would rather pay to get their gifts professionally wrapped instead of doing it themselves....there's a business idea for anyone looking for some cash work over the holidays....
Rob
-
A lot of the department stores in the U.S. used to have a special "shop" in the store that would do that... sometimes, for free.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply