query to get top player

[pbuscio]

I have a query that shows the players in my pool league ranked by winning pct. what i am looking for is a new query, or changing the existing query to sho the top ranked player for each team only. here is the SQL and a sample output

SELECT tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
FROM tblPlayerStats_2
GROUP BY tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
ORDER BY tblPlayerStats_2.Rank;



Rank PlayerName Team GP Won Lost Pct
1 Ed Sheehan Doms B 24 21 3 0.88
2 Tom Bow Kat A 23 20 3 0.87
3 Fran Convery Kat B 20 17 3 0.85
3 Mike Monell Doms B 20 17 3 0.85
5 Nick Jankowski McMichaels 11 9 2 0.82
6 John Grassia McMichaels 24 19 5 0.79
7 Mike Pryzwara Sharkys 19 15 4 0.79
7 Tony Zubec Riverside 19 15 4 0.79
9 Warren Darnell McMichaels 23 18 5 0.78
10 John Herrmann Riverside 24 18 6 0.75
10 Nick Comstock Sharkys 24 18 6 0.75

 

[Gene Wirchenko]

I have a query that shows the players in my pool league ranked by winning pct. what i am looking for is a new query, or changing the existing query to sho the top ranked player for each team only. here is the SQL and a sample output

SELECT tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
FROM tblPlayerStats_2
GROUP BY tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName, tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won, tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
ORDER BY tblPlayerStats_2.Rank;



Rank PlayerName Team GP Won Lost Pct
1 Ed Sheehan Doms B 24 21 3 0.88
2 Tom Bow Kat A 23 20 3 0.87
3 Fran Convery Kat B 20 17 3 0.85
3 Mike Monell Doms B 20 17 3 0.85
5 Nick Jankowski McMichaels 11 9 2 0.82
6 John Grassia McMichaels 24 19 5 0.79
7 Mike Pryzwara Sharkys 19 15 4 0.79
7 Tony Zubec Riverside 19 15 4 0.79
9 Warren Darnell McMichaels 23 18 5 0.78
10 John Herrmann Riverside 24 18 6 0.75
10 Nick Comstock Sharkys 24 18 6 0.75

I did this in SQL Server 2008 Express so there may be syntax
differences. You will also have to adjust names as "rank" is a
reserved word in SQL Server.

This would have been easier if you had included the DDL for the
table and inserts for the data as I did below. If I had not been
curious about a point, I would not have bothered. (Make it easy for
people to help you.)

***** Start of Code *****
use tempdb

drop table #Stats
drop table #Grouped

create table #Stats
(
theRank int not null,
PlayerName nvarchar(max) not null,
Team nvarchar(max) not null,
GP int not null,
Won int not null,
Lost int not null,
Pct numeric(4,2) not null
)

insert into #Stats
(theRank,PlayerName,Team,GP,Won,Lost,Pct)
values
(1,'Ed Sheehan','Doms B',24,21,3,0.88),
(2,'Tom Bow','Kat A',23,20,3,0.87),
(3,'Fran Convery','Kat B',20,17,3,0.85),
(3,'Mike Monell','Doms B',20,17,3,0.85),
(5,'Nick Jankowski','McMichaels',11,9,2,0.82),
(6,'John Grassia','McMichaels',24,19,5,0.79),
(7,'Mike Pryzwara','Sharkys',19,15,4,0.79),
(7,'Tony Zubec','Riverside',19,15,4,0.79),
(9,'Warren Darnell','McMichaels',23,18,5,0.78),
(10,'John Herrmann','Riverside',24,18,6,0.75),
(10,'Nick Comstock','Sharkys',24,18,6,0.75)

select Team,max(Pct) as maxPct into #Grouped from #Stats
group by Team

select theRank,PlayerName,Stats.Team,GP,Won,Lost,Pct from #Stats as
Stats
inner join #Grouped as Grouped
on Stats.Team=Grouped.Team and Stats.Pct=Grouped.maxPct
order by theRank
***** End of Code *****

Sincerely,

Gene Wirchenko

 

[pbuscio]

Not sure what you mean by DDL. I gave the exact SQL from Access and a sample output. Not sure what else I could have given. But i will try what you said and see if that works for me. Thanks for the help.

 

[Bob Barrows]

Not sure what you mean by DDL. I gave the exact SQL from Access and a
sample output. Not sure what else I could have given. But i will try
what you said and see if that works for me. Thanks for the help.

DDL = Data Definitition Language
as opposed to
DML = Data Modification Language

DDL consists of CREATE TABLE statements (among others) that are easy to
generate in SQL Server client tools, but not so easy with Access. DDL in an
Access group is a bit too much to expect, Gene.
I'm happy when sample data and expected results are posted in tabular
format; at least I can paste the sample data into Excel and then import it
into Access after cleaning it up.
The rarity of that occurence does mean it's too much too expect as well, but
it is easier to accomplish than generating DDL.

 

[Gene Wirchenko]

On Sat, 18 Aug 2012 11:32:08 -0700 (PDT), (e-mail address removed) wrote:

[snip]

Not sure what you mean by DDL. I gave the exact SQL from Access and a

sample output. Not sure what else I could have given. But i will try
what you said and see if that works for me. Thanks for the help.

DDL = Data Definition Language. Defining the table.

If you look at what I posted, you will see that it is not
necessary to do anything but copy and paste into SSMS (SQL Server's
front-end program). That is because I create the table and the data.
This would have been a good idea for you to do because:

1) It saves everyone else's time. Remember that we are not getting
paid for this. If I had not been specifically curious about an aspect
of your issue, I probably would not have bothered to post. Make it
easy for us.

2) It helps catch stupid mistakes. You might have a syntax error
that is not obvious. This is not so applicable to your case since you
posted code, but some people do not and still ask.

3) It shows what the datatypes are. It could be that you make a
mistake with datatypes or their sizes. We are not so likely to catch
such errors.

4) It requires that you state your problem clearly. Many times, I
have written a request for help on something, and in the process of
writing it up so that another could easily see the problem, I find out
a critical bit of information and am able to solve the problem myself.
Even if it does not, it helps me focus more closely on the problem.

It would be good for you to read and apply:

How To Ask Questions The Smart Way
by Eric Steven Raymond
and
http://sscce.org/
The SSCCE
Short, Self Contained, Correct (Compilable), Example
They are references on how to ask for technical help.

Sincerely,

Gene Wirchenko

 

[Bob Barrows]

If you look at what I posted, you will see that it is not
necessary to do anything but copy and paste into SSMS (SQL Server's
front-end program).

At this point, you just lost the OP, who is obviously a novice Access user
and likely has no intention of installing SSMS on his machine, something
that would require him to learn yet another tool while trying to learn how
to use Access.
The key takeaway: do not expect DDL in an Access group. Access, along with
VBA, provides so many ways to create metadata that most Access users will
never see a single line of DDL. It's not like SQL Server, where the only
programming language we have available out of the box is T-SQL, which
requires us to learn and use DDL from a very early point in our SQL Server
careers.

 

[Gene Wirchenko]

At this point, you just lost the OP, who is obviously a novice Access user
and likely has no intention of installing SSMS on his machine, something
that would require him to learn yet another tool while trying to learn how
to use Access.
The key takeaway: do not expect DDL in an Access group. Access, along with
VBA, provides so many ways to create metadata that most Access users will
never see a single line of DDL. It's not like SQL Server, where the only
programming language we have available out of the box is T-SQL, which
requires us to learn and use DDL from a very early point in our SQL Server
careers.

Be that as it may, without DDL, I am likely to skip anything for
which the answer is fairly obvious to me. OP got lucky this time. It
will not always be so.

Sincerely,

Gene Wirchenko

 

[Bob Barrows]

I have a query that shows the players in my pool league ranked by
winning pct. what i am looking for is a new query, or changing the
existing query to sho the top ranked player for each team only. here
is the SQL and a sample output

SELECT tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName,
tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won,
tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
FROM tblPlayerStats_2
GROUP BY tblPlayerStats_2.Rank, tblPlayerStats_2.PlayerName,
tblPlayerStats_2.Team, tblPlayerStats_2.GP, tblPlayerStats_2.Won,
tblPlayerStats_2.Lost, tblPlayerStats_2.Pct
ORDER BY tblPlayerStats_2.Rank;



Rank PlayerName Team GP Won Lost Pct
1 Ed Sheehan Doms B 24 21 3 0.88
2 Tom Bow Kat A 23 20 3 0.87
3 Fran Convery Kat B 20 17 3 0.85
3 Mike Monell Doms B 20 17 3 0.85
5 Nick Jankowski McMichaels 11 9 2 0.82
6 John Grassia McMichaels 24 19 5 0.79
7 Mike Pryzwara Sharkys 19 15 4 0.79
7 Tony Zubec Riverside 19 15 4 0.79
9 Warren Darnell McMichaels 23 18 5 0.78
10 John Herrmann Riverside 24 18 6 0.75
10 Nick Comstock Sharkys 24 18 6 0.75

Well, you got the SQL 2008 answer, which is totally irrelevant for Access. I
will now try for the Access answer.
So can I surmise that your intended results look like this?
Doms B Ed Sheehan 24 21 3 0.88
Kat A Tom Bow 23 20 3 0.87
Kat B Fran Convery 20 17 3 0.85
McMichaels Nick Jankowski 11 9 2 0.82
Sharkys Mike Pryzwara 19 15 4 0.79
Riverside Tony Zubec 19 15 4 0.79

What happens when two players tie for the top for a team? Do you wish to
show them both? I will assume so

If so, you need one saved query to provide the top rank per team. Use this
sql to create a saved query called qTopRankPerTeam:
SELECT Team, Max(Rank) As TopRank
FROM tblPlayerStats_2
GROUP BY Team

Then, in another query, use this sql to join the saved query (aka "view") to
your table:

SELECT t.Team, PlayerName,GP, Won,Lost, Pct
FROM tblPlayerStats_2 As t INNER JOIN qTopRankPerTeam As q
ON t.Team=q.Team and t.Rank = q.TopRank
ORDER BY t.Rank

If that does not provide the correct answer, then I will need to see your
sample data and desired results, both in tabular format as you provided the
sample data above.

PS. This could be done using a derived table in a single query, but I find
the multi-query technique a bit easier to follow. If you want to see how to
do it in a single query, here goes:

SELECT t.Team, PlayerName,GP, Won,Lost, Pct
FROM tblPlayerStats_2 As t INNER JOIN (
SELECT Team, Max(Rank) As TopRank
FROM tblPlayerStats_2
GROUP BY Team
) As q
ON t.Team=q.Team and t.Rank = q.TopRank
ORDER BY t.Rank

See? Just take the sql from the saved query and wrap it in parentheses,
making sure to use an alias (the "As q" bit).

 

[Bob Barrows]

Be that as it may, without DDL, I am likely to skip anything for

Perhaps that's for the best ...

which the answer is fairly obvious to me. OP got lucky this time. It
will not always be so.

Actually, he didn't get lucky. I finally got around to reading your entire
initial reply and realized you gave him a bunch of t-sql, with temp tables
(!?) no less, that he could not possibly use, let alone understand.