select query top values no duplicates

M

Mark

Small prob - any help guys?
I have a query like below.however say the one dog has the same best rating
more than once, my curent query brings up both those values.but i would like
it to only select one of the dogs best runs based on the best rating and the
most recent date.so this query works as it should but how do i get it to only
select the one with the most recent date??

SELECT Tfieldssel.Dog, TfieldsseldogTable.Rating AS BestRating,
TfieldsseldogTable.W_DATE AS bestdate1, TfieldsseldogTable.W_TRACK AS
besttrack1, TfieldsseldogTable.W_WGHT_CAR AS bestwt1,
tfieldsseldogTable.W_BOOK_PRI AS bestsp1, TfieldsseldogTable.TrackRating AS
besttr1
FROM Tfieldssel LEFT JOIN TfieldsseldogTable ON Tfieldssel.dog =
TfieldsseldogTable.dog
WHERE (((TfieldsseldogTable.Badj) In (select top 1 rating from
tfieldsseldogtable where tfieldssel.dog=tfieldsseldogtable.dog order by badj
desc) Or (TfieldsseldogTable.Badj) Is Null))
ORDER BY Tfieldssel.dog;
 
G

Gary Walter

Mark said:
Small prob - any help guys?
I have a query like below.however say the one dog has the same best rating
more than once, my curent query brings up both those values.but i would
like
it to only select one of the dogs best runs based on the best rating and
the
most recent date.so this query works as it should but how do i get it to
only
select the one with the most recent date??

SELECT Tfieldssel.Dog, TfieldsseldogTable.Rating AS BestRating,
TfieldsseldogTable.W_DATE AS bestdate1, TfieldsseldogTable.W_TRACK AS
besttrack1, TfieldsseldogTable.W_WGHT_CAR AS bestwt1,
tfieldsseldogTable.W_BOOK_PRI AS bestsp1, TfieldsseldogTable.TrackRating
AS
besttr1
FROM Tfieldssel LEFT JOIN TfieldsseldogTable ON Tfieldssel.dog =
TfieldsseldogTable.dog
WHERE (((TfieldsseldogTable.Badj) In (select top 1 rating from
tfieldsseldogtable where tfieldssel.dog=tfieldsseldogtable.dog order by
badj
desc) Or (TfieldsseldogTable.Badj) Is Null))
ORDER BY Tfieldssel.dog;
Click to expand...

Hi Mark,

I'd probably just "divide and conquer."

qryMaxRating

SELECT
Tf.Dog,
Tf.Rating,
Tf.W_DATE,
Tf.W_TRACK,
Tf.W_WGHT_CAR,
Tf.W_BOOK_PRI
Tf.TrackRating
FROM
TfieldsseldogTable As Tf
WHERE
Tf.Rating =
(SELECT Max(t.Rating)
FROM
TfieldsseldogTable As t
WHERE
t.Dog = Tf.Dog);

Can one assume get max rating only on one date
(or does W_DATE include time portion also)?
if so, then...

qryMaxRatingDate

SELECT
q.Dog,
q.Rating,
q.W_DATE,
q.W_TRACK,
q.W_WGHT_CAR,
q.W_BOOK_PRI
q.TrackRating
FROM
qryMaxRating As q
WHERE
q.W_DATE =
(SELECT Max(t.W_DATE)
FROM
qryMaxRating As t
WHERE
t.Dog = q.Dog);

then left join Tfieldssel to qryMaxRatingDate.

If you can have more than one max rating for
a specific dog on the same date, then you
will need to decide what other field will determine
which record you return from qryMaxRatingDate.

good luck,

gary
 
Top