Reader Challenge Part II - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reader Challenge Part II

Author

Topic

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 02:45:29

Jeff (jsmith8858) provided a very elegant solution to a question I posted earlier [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37259[/url]
I thought I would post another difficult (to me anyway!) problem I ran into recently.

----------------------
Given: A Table Name: grades

student_id int(4) pk
student_category nvarchar(20)
firstname nvarchar (20)
lastname nvarchar (20)
score decimal

Sample data:

1 beginner bob taylor 70
2 beginner bob taylor 80
3 beginner lucy arnez 70
4 beginner robert owens 60
5 advanced anita landis 90
6 advanced bob wood 90
7 advanced barbara lyle 85
8 advanced barbara lyle 83

The query should return a list, grouped by student_category, containing the AVG(score) sorted descending. But, we would like to limit the results to the Top 2 Averages in each student_category. BUT,if there is a tie - for the average, then then the records for ALL students that tie, should be included. I'm beginning to wonder if this CAN be done is a single statement using derived queries?

Desired output:
student_category student lastname, first name average_score
advanced anita landis 90
advanced bob wood 90
advanced barbara lyle 84
beginner bob taylor 75
beginner lucy arnez 70
--------------
Here is some DDL


create table grades(
student_id   int,
student_category nvarchar(20),
firstname nvarchar (20),
lastname nvarchar (20),
score  decimal)

insert into grades values(1, 'beginner', 'bob','taylor', 70)
insert into grades values(2, 'beginner', 'bob','taylor' ,80)
insert into grades values(3, 'beginner', 'lucy','arnez', 70)
insert into grades values(4, 'beginner', 'robert','owens', 60)
insert into grades values(5, 'advanced', 'anita','landis', 90)
insert into grades values(6, 'advanced', 'bob','wood', 90)
insert into grades values(7, 'advanced', 'barbara','lyle', 85)
insert into grades values(8, 'advanced', 'barbara','lyle', 83)

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-07-12 : 08:38:35

Here you go.... more! more!

create table #grades(
student_id int,
student_category nvarchar(20),
firstname nvarchar (20),
lastname nvarchar (20),
score decimal)

insert into #grades values(1, 'beginner', 'bob','taylor', 70)
insert into #grades values(2, 'beginner', 'bob','taylor' ,80)
insert into #grades values(3, 'beginner', 'lucy','arnez', 70)
insert into #grades values(4, 'beginner', 'robert','owens', 60)
insert into #grades values(5, 'advanced', 'anita','landis', 90)
insert into #grades values(6, 'advanced', 'bob','wood', 90)
insert into #grades values(7, 'advanced', 'barbara','lyle', 85)
insert into #grades values(8, 'advanced', 'barbara','lyle', 83)

Select *
From
(
Select
A.*,
place = (Select count(Distinct score)+1 From (Select student_category, firstName, lastName, score = avg(score) From #grades Group By student_category, firstName, lastName) as B Where student_category = A.student_category and score > A.score)
From (Select student_category, firstName, lastName, score = avg(score) From #grades Group By student_category, firstName, lastName) as A
) as Z
Where place in (1,2)
Order By student_category, score desc

Drop Table #grades

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources