Distinct Rows ??

[carrajo]

Here's my data

FName LName SCORE City State Date
JOE SCHMOE 20 SUSSEX WI 7/9/2005
FRANK GRIMES 19 SUSSEX WI 7/11/2005
DAVID SMITH 18 RACINE WI 7/14/2005
JOE SCHMOE 25 SUSSEX WI 7/10/2005
FRANK GRIMES 18 FRANKSVILLE WI 7/12/2005
DAVID SMITH 19 FRANKSVILLE WI 7/16/2005
CHRIS DAVIS 30 FRANKSVILLE WI 7/16/2005

I basically want to return the unique row where score is the highest.
The data should be returned is:

CHRIS DAVIS 30 FRANKSVILLE WI 7/16/2005
JOE SCHMOE 25 SUSSEX WI 7/10/2005
FRANK GRIMES 19 SUSSEX WI 7/11/2005
DAVID SMITH 19 FRANKSVILLE WI 7/16/2005

I'm using MS Access 2000

Oh, by the way. Is there any way I can return only the top 20 or do I
need to control this via
my .asp script.

Thanks for your help

[Bob Quintal]

"carrajo" <ca*******@gmail.comwrote in
news:11********************@b28g2000cwb.googlegrou ps.com:

Here's my data

FName LName SCORE City State Date
JOE SCHMOE 20 SUSSEX WI 7/9/2005
FRANK GRIMES 19 SUSSEX WI 7/11/2005
DAVID SMITH 18 RACINE WI 7/14/2005
JOE SCHMOE 25 SUSSEX WI 7/10/2005
FRANK GRIMES 18 FRANKSVILLE WI 7/12/2005
DAVID SMITH 19 FRANKSVILLE WI 7/16/2005
CHRIS DAVIS 30 FRANKSVILLE WI 7/16/2005

I basically want to return the unique row where score is the
highest. The data should be returned is:

CHRIS DAVIS 30 FRANKSVILLE WI 7/16/2005
JOE SCHMOE 25 SUSSEX WI 7/10/2005
FRANK GRIMES 19 SUSSEX WI 7/11/2005
DAVID SMITH 19 FRANKSVILLE WI 7/16/2005

I'm using MS Access 2000

Oh, by the way. Is there any way I can return only the top 20
or do I need to control this via
my .asp script.

Thanks for your help

You have to do this in steps.
The first is to return the maximum score for each user.

SELECT Fname, lname, max(score)
FROM tablename
GROUP BY Fname, Lname;

then go back and get the other info like address and date, for each
instance of Fname, Lname and score.

SELECT * FROM tablename
INNER JOIN queryname
ON tablename.fname = queryname.fname
AND tablename.Lname = queryname.Lname
AND tablename.score = queryname.score
ORDER BY Score DESC;

To limit the resultset, add a TOP n predicate to the select.
as in SELECT TOP 20 * FROM tablename.

If you feel really ambitious and don't want the first step query
around you can try substituting the code so that you get

SELECT TOP 20 * FROM tablename
INNER JOIN (SELECT Fname, lname, max(score)
FROM tablename
GROUP BY Fname, Lname) ALIAS queryname
ON tablename.fname = queryname.fname
AND tablename.Lname = queryname.Lname
AND tablename.score = queryname.score
ORDER BY Score DESC;

Problem is that this type of nested query confuses the Query Design
Viewer, and I don't think it's worth the effort.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[David W. Fenton]

Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

If you feel really ambitious and don't want the first step query
around you can try substituting the code so that you get

SELECT TOP 20 * FROM tablename
INNER JOIN (SELECT Fname, lname, max(score)
FROM tablename
GROUP BY Fname, Lname) ALIAS queryname
ON tablename.fname = queryname.fname
AND tablename.Lname = queryname.Lname
AND tablename.score = queryname.score
ORDER BY Score DESC;

Problem is that this type of nested query confuses the Query
Design Viewer, and I don't think it's worth the effort.

Eh? It doesn't confuse the QBE at all. At least, as long as you use
[]. As Alias instead of the more widely used ().

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

>If you feel really ambitious and don't want the first step
query around you can try substituting the code so that you
get

SELECT TOP 20 * FROM tablename
INNER JOIN (SELECT Fname, lname, max(score)
FROM tablename
GROUP BY Fname, Lname) ALIAS queryname
ON tablename.fname = queryname.fname
AND tablename.Lname = queryname.Lname
AND tablename.score = queryname.score
ORDER BY Score DESC;

Problem is that this type of nested query confuses the Query
Design Viewer, and I don't think it's worth the effort.

Eh? It doesn't confuse the QBE at all. At least, as long as
you use []. As Alias instead of the more widely used ().

Thank you for the pointer sir.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com