Reducing to One Query

[Gene Wirchenko]

The following is my reply to a help request in an Access group.
My solution does work in SSE2008, but I would like to know if it is
possible to reduce it to one select rather than the two I used.

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

[rpresser]

Post by Gene Wirchenko
The following is my reply to a help request in an Access group.
My solution does work in SSE2008, but I would like to know if it is
possible to reduce it to one select rather than the two I used.

Very straightforward:


select theRank,PlayerName,Stats.Team,GP,Won,Lost,Pct
from #Stats as Stats
inner join (select Team,max(Pct) as maxPct from #Stats
group by Team) as Grouped
on Stats.Team=Grouped.Team and Stats.Pct=Grouped.maxPct
order by theRank

[Gene Wirchenko]

On Thu, 16 Aug 2012 15:52:28 -0700 (PDT), rpresser

Post by Gene Wirchenko

Post by Gene Wirchenko
The following is my reply to a help request in an Access group.
My solution does work in SSE2008, but I would like to know if it is
possible to reduce it to one select rather than the two I used.

select theRank,PlayerName,Stats.Team,GP,Won,Lost,Pct
from #Stats as Stats
inner join (select Team,max(Pct) as maxPct from #Stats
group by Team) as Grouped
on Stats.Team=Grouped.Team and Stats.Pct=Grouped.maxPct
order by theRank

Well, now it is. Another poster posted something quite similar.

Thank you.

Sincerely,

Gene Wirchenko