Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1217

Reducing to One Query

Cross-posted to 2 groups.

Show all headers | View raw

     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.

On Thu, 16 Aug 2012 12:25:30 -0700 (PDT), pbuscio@comcast.net wrote:

>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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Reducing to One Query Gene Wirchenko <genew@ocis.net> - 2012-08-16 13:54 -0700
  Re: Reducing to One Query Philipp Post <post.philipp@googlemail.com> - 2012-08-17 06:23 -0700
    Re: Reducing to One Query Gene Wirchenko <genew@ocis.net> - 2012-08-17 09:15 -0700

csiph-web