Work out last race winner 2

[Sammy145]

I need help in working out of a list of 100000 dog races, which dog has won there races what i need is

1. winner of Previous race (needed as coloumn (Yes/No)
2. winner of previous 3 races (needed as coloumn (Yes/No)
3. winner of previous 6 races (needed as coloumn (Yes/No)

Finish position = 1 is WINNER
There could be gaps in race iD

Data format

dogID | Finish position | Race ID |
1 | 1 3
1 | 1 4
1 1 5
1 4 6
1 1 7


Thanks

 

[gmmastros]

Is this for a class? If so, which one?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Sammy145]

What do u mean a class? I need to work on a query that does this TSQL

All i want to return is for points 1 an 2 is YES and for point 3 NO

 

[simian336]

George was asking if this is for school, we have a tradition of helping students but not out right giving them answers....

Simi

 

[Sammy145]

Hi mate, not for school for a job.


Sam

 

[simian336]

I think I am understanding but could you show your expected result.

Simi

 

[Sammy145]

Yes it's a tricky one Here what i would expect as coloumns

dogID |Finish position|RaceID|WinnerLastrace|Winnerlast3|winlast6
1 | 1 3 No No No
1 | 1 4 Yes No No
1 1 5 Yes No No
1 4 6 Yes Yes No
1 1 7 No Yes No

I will need to do a batch update on a new table with this query.

Any help much appreciated, thanks for looking into this.

Sam

 

[Sammy145]

Any HELP Please ????

 

[r937]

how do you propose to identify the "previous" race?

there's no date column

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Sammy145]

could we use a lookup table that had Raceid AND SORT BY ASC

Sam

 

[Rofeu]

Hi,

there seems to be a logical inconsistency in your example.
Does Winnerlast3 mean: Did the dog win the last 3 races? OR
Did the dog win one of the last three races?

In case 1 the last record is incorrect in case 2 records 2 and 3 are incorrect. Please clarify.

In case 2 you could use something along the lines of:

select		R.*
	,		case
			(select min(R1.Finish) 
			 from RaceResults R1 
			 where R1.RaceId = (R.RaceId - 1) and R1.Dog = R.Dog)
			when 1 then 'YES' else 'NO' end
	,		case
			(select min(R1.Finish) 
			 from RaceResults R1 
			 where R1.RaceId between (R.RaceId - 4) and (R.RaceId - 1) and R1.Dog = R.Dog)
			when 1 then 'YES' else 'NO' end
	,		case
			(select min(R1.Finish) 
			 from RaceResults R1 
			 where R1.RaceId between (R.RaceId - 7) and (R.RaceId - 1) and R1.Dog = R.Dog)
			when 1 then 'YES' else 'NO' end
from RaceResults R

If case 1 is what you're after just replace the 'min' with 'avg'

(untested query, but should be ok)

Cheers,

Rofeu

 

[drlex]

Rofeu,
The bit about "[t]here could be gaps in race iD" means that using a join on such would lead to problems. I tried amending your code to use a row_number from partition by, but it got so ugly I'm too ashamed to post it!


soi là, soi carré

 

[Rofeu]

hmmm, missed that part. How about this:

select        R.*    
,        case
            (select min(R1.Finish)
              from (select top 1 R2.Finish from RaceResults R2              
              where R1.RaceId < R.RaceId and R1.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end
,        case
            (select min(R1.Finish)
              from (select top 3 R2.Finish from RaceResults R2              
              where R1.RaceId < R.RaceId and R1.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end
,        case
            (select min(R1.Finish)
              from (select top 6 R2.Finish from RaceResults R2              
              where R1.RaceId < R.RaceId and R1.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end              
from RaceResults R

Cheers,

Rofeu

 

[Rofeu]

Sorry, forgot to amend some references:

select        R.*    
,        case
            (select min(R1.Finish)
              from (select top 1 R2.Finish from RaceResults R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end
,        case
            (select min(R1.Finish)
              from (select top 3 R2.Finish from RaceResults R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end
,        case
            (select min(R1.Finish)
              from (select top 6 R2.Finish from RaceResults R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog)R1)           
              when 1 then 'YES' else 'NO' end              
from RaceResults R

 

[drlex]

Think you need MAX and ORDER BY, plus a COUNT to ensure that you are getting 3 or 6 prior races.

select        R.*    
,        case
            (select max(R1.Finish)
              from (select top 1 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              when 1 then 'YES' else 'NO' end as [won last 1]
,        case when
            (select max(R1.Finish)
              from (select top 3 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1) 
              = 1 and 
			(select count(R1.Finish)
              from (select top 6 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              = 3 then 'YES' else 'NO' end as [won last 3]
,        case when 
            (select max(R1.Finish)
              from (select top 6 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              = 1 and 
			(select count(R1.Finish)
              from (select top 6 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              = 6 then 'YES' else 'NO' end as [won last 6]           
from RaceResults R

I'm afraid my additions make it look awful!

soi là, soi carré

 

[Rofeu]

Hi,

you're right about the order by clause, but not the max. You want to know if there is an instance where the dog has come in at place 1, right? The COUNT seems redundant, as if the dog came in at place 1 in a race in less than 3 respectively 6 races, it would come in at place 1 in a race in 3 respectively 6 races as well. Unless I'm mistaken in my understanding of what it is you're trying to achieve...(i.e. the dog had to win ALL of the previous 3 respectively 6 races, in which case you can sum the places and divide by 3 respectively 6 instead of using max, which will eliminate the need for the COUNT)

Cheers,

Rofeu

 

[drlex]

The problem is that top 3 or 6 don't always return 3 or 6 rows, so a COUNT is necessary to test the number of rows.
The MAX is to ensure that one gets the highest position, otherwise a MIN could return the first place, and not indicate a placement of 2 or greater.
A slight adjustment:

select        R.*    
,        case
            (select max(R1.Finish)
              from (select top 1 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              when 1 then 'YES' else 'NO' end as [won last 1]
,        case when
            (select count(1)/max(R1.Finish)
              from (select top 3 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1) 
              = 3 then 'YES' else 'NO' end as [won last 3]
,        case when 
            (select count(1)/max(R1.Finish)
              from (select top 6 R2.Finish from RaceResults
R2              
              where R2.RaceId < R.RaceId and R2.Dog = R.Dog order by RaceId desc)R1)           
              = 6 then 'YES' else 'NO' end as [won last 6]           
from RaceResults R

Try testing with a quick temp table.

create table RaceResults (dog int, [Finish] int,  [RaceID] int)
Insert into RaceResults 
select 
1 ,1 ,3 union all select
1, 1, 4 union all select
1, 1, 5 union all select
1, 4, 6 union all select
1, 1, 7 union all select
1, 1, 8	union all select
1, 1, 12 union all select
1, 1, 14 union all select
1, 1, 15 union all select
1, 1, 16 union all select
1, 1, 18

soi là, soi carré

 

[Sammy145]

Drlex Tested and your query produces what I need.

I did mange to get the previous row by using this query below. I have amended it to your table drlex BUT wasn' able to work out how to get the next criteria's such as 3 races or 6

ps I can use multiple temp tables for this requirement.

With tblDifference as
(
Select Row_Number() OVER (Order by dog) as RowNumber,RaceID, dog, Finish from RaceResults
)

Select
Cur.RaceID as Currentmyid,
Prv.RaceID as Previousmyid,
Cur.dog as Currenthorseid,
Prv.dog as Previoushorseid,
Cur.Finish as FinishPosition, Prv.Finish as PreviousFinishPosition,
Cur.Finish-Prv.Finish as Difference

from
tblDifference Cur Left Outer Join tblDifference Prv
On Cur.RowNumber=Prv.RowNumber+1

I'll post back if I run into any other issues today.

Thanks guys for your help much appreciated!

 

[Rofeu]

please ignore the last comment in between brackets. This is obviously flawed.

I was working under the impression that you needed to check if a dog had won a race in the last 3 or 6 races, in which case the min is what you need. I understand now that the dog has to have won ALL of the previous 3 or 6 races. I was going to propose a division, glad to see you came up with that too.

The revised sql you posted should work fine.

Cheers,

Rofeu

 

[Sammy145]

Thanks drlex & Rofeu for looking into this and coming up with a solution much appreciated.