Subset query

G

Guest

I am using the following SQL in Access to run a query. I would like to limit
the number of records returned in the Horse column to the lastest 25
(specified by DateOfRace). So I'm NOT limiting the overall query to 25
records, but a subset groups of records in each Horse column.

Any ideas how I would accomplish this. I tried the TOP command but that
limits the overall query to 25 records.

SELECT Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum, Profiler.Horse,
Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey, RaceChart.DateOfRace,
RaceChart.Track, RaceChart.RaceNum, RaceChart.RaceClassification,
RaceChart.Dist, RaceChart.Horse, RaceChart.FinishPos, RaceChart.[Win$],
Count("Rank:") AS Expr1
FROM Profiler LEFT JOIN RaceChart ON Profiler.Trainer = RaceChart.Trainer
GROUP BY Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum,
Profiler.Horse, Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey,
RaceChart.DateOfRace, RaceChart.Track, RaceChart.RaceNum,
RaceChart.RaceClassification, RaceChart.Dist, RaceChart.Horse,
RaceChart.FinishPos, RaceChart.[Win$]
HAVING (((Count("Rank:"))>=20))
ORDER BY Count("Rank:");
 
G

Guest

This is the current SQL I'm am using. The previous SQL does not run.
Sorry...

SELECT Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum, Profiler.Horse,
Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey, RaceChart.DateOfRace,
RaceChart.Track, RaceChart.RaceNum, RaceChart.RaceClassification,
RaceChart.Dist, RaceChart.Horse, RaceChart.FinishPos, RaceChart.[Win$]
FROM Profiler LEFT JOIN RaceChart ON Profiler.Trainer = RaceChart.Trainer
GROUP BY Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum,
Profiler.Horse, Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey,
RaceChart.DateOfRace, RaceChart.Track, RaceChart.RaceNum,
RaceChart.RaceClassification, RaceChart.Dist, RaceChart.Horse,
RaceChart.FinishPos, RaceChart.[Win$];
 
G

Guest

You can use an inline view to create a view that is the Top 25 horses based
on whatever criteria you define. Not sure exactly what you want but maybe
this example will give you an idea what I mean:

Select track, racenumber, horse, odds, trainer, dateofrace

from profiler inner join (select top 25 trainer, horse,dateofrace from
racechart order by dateofrace desc) A on profiler.[trainer] = a.[trainer]

Backin said:
This is the current SQL I'm am using. The previous SQL does not run.
Sorry...

SELECT Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum, Profiler.Horse,
Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey, RaceChart.DateOfRace,
RaceChart.Track, RaceChart.RaceNum, RaceChart.RaceClassification,
RaceChart.Dist, RaceChart.Horse, RaceChart.FinishPos, RaceChart.[Win$]
FROM Profiler LEFT JOIN RaceChart ON Profiler.Trainer = RaceChart.Trainer
GROUP BY Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum,
Profiler.Horse, Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey,
RaceChart.DateOfRace, RaceChart.Track, RaceChart.RaceNum,
RaceChart.RaceClassification, RaceChart.Dist, RaceChart.Horse,
RaceChart.FinishPos, RaceChart.[Win$];

--
Thanks!


Backin said:
I am using the following SQL in Access to run a query. I would like to limit
the number of records returned in the Horse column to the lastest 25
(specified by DateOfRace). So I'm NOT limiting the overall query to 25
records, but a subset groups of records in each Horse column.

Any ideas how I would accomplish this. I tried the TOP command but that
limits the overall query to 25 records.

SELECT Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum, Profiler.Horse,
Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey, RaceChart.DateOfRace,
RaceChart.Track, RaceChart.RaceNum, RaceChart.RaceClassification,
RaceChart.Dist, RaceChart.Horse, RaceChart.FinishPos, RaceChart.[Win$],
Count("Rank:") AS Expr1
FROM Profiler LEFT JOIN RaceChart ON Profiler.Trainer = RaceChart.Trainer
GROUP BY Profiler.Track, Profiler.RaceNumber, Profiler.PgmNum,
Profiler.Horse, Profiler.MLOdds, Profiler.Trainer, Profiler.Jockey,
RaceChart.DateOfRace, RaceChart.Track, RaceChart.RaceNum,
RaceChart.RaceClassification, RaceChart.Dist, RaceChart.Horse,
RaceChart.FinishPos, RaceChart.[Win$]
HAVING (((Count("Rank:"))>=20))
ORDER BY Count("Rank:");
Click to expand...
Click to expand...
 
Top