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]

Re: Distinct Rows ??

"carrajo" <carrazola@gmai l.comwrote in
news:1152818430 .256474.6540@b2 8g2000cwb.googl egroups.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]

Re: Distinct Rows ??

Bob Quintal <rquintal@sPAmp atico.cawrote in
news:Xns97FF9F8 E3CA63BQuintal@ 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]

Re: Distinct Rows ??

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in
news:Xns97FFCA3 BD60F9f99a49ed1 d0c49c5bbb2@127 .0.0.1:

Bob Quintal <rquintal@sPAmp atico.cawrote in
news:Xns97FF9F8 E3CA63BQuintal@ 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