T-SQL help please! Top 25 individuals query

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)

T-SQL help please! Top 25 individuals query

Author

Topic

dlane77
Starting Member

1 Post

Posted - 2004-08-25 : 04:27:14

I'm working on a race database set up like this:

Courses table: CourseID, ...
Events table: EventID, CourseID, EventDate, ...
EventRunners: EventID, RunnerID, ElapsedTime, Gender, Grade, TeamID, ...
Runners: RunnerID, FName, LName
Teams: TeamID, TeamName, ...

What I'm querying with is:
select top 25 min(er.elapsedTime), er.grade, er.runnerid, r.fname, r.lname, t.teamname
from eventrunners er
inner join runners r on r.runnerid = er.runnerid
inner join teams t on t.teamid = er.teamid
where eventid in
(select eventid from events where courseID = 5) and er.runnerid in
(select runnerid from runners where gender = 'M')
group by er.runnerID, r.fname, r.lname, t.teamname, er.grade
order by min(er.elapsedTime) asc

What I'm shooting for is a list of the top 25 performances on a given course by men or women. The problem is I'm getting duplicates if someone has two top 25 finishes from two different grades (sophomore and junior, for instance) or teams. How can I only return one record per person and the grade and team they ran with when they got that time? Thank you very much in advance.

-Doug

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-25 : 05:28:44

post create table, sample data(insert into ...), and desired result. that's the fastest way you'll get a correct answer.
on the first look it looks like you'll need to join on subquery. something like:

inner join (select runnerId, max(grade) as grade, max(TeamId) as TeamId from eventrunners group by runnerId)) a on (a.runnerId = er.runnerId) and (a.grade = er.grade) and (a.TeamId = er.TeamId)

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 08:41:07

I think this will do it, but like spirit said, sample data and desired results are much more helpful...


Select 
	top 25 *
From
	(
	select 
		er.elapsedTime, 
		er.grade, 
		er.runnerid, 
		r.fname, 
		r.lname, 
		t.teamname,
		isRunnerMin = case when not exists(Select 1 From eventrunners where elapsedTime < er.elapsedTime and runnerid = er.runnerid) then 1 else 0 end
	from eventrunners er 
	inner join runners r 
	on r.runnerid = er.runnerid
	inner join teams t 
	on t.teamid = er.teamid
	where eventid in
	(select eventid from events where courseID = 5) 
	and er.runnerid in (select runnerid from runners where gender = 'M')
	) as A
Where isRunnerMin=1

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-25 : 08:54:27

ok why select 1 in isRunnerMin = case when not exists(Select 1 From eventrunners ... ??

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-25 : 09:00:59

Well i read it here one time... but I don't remember where.

Basically, I am only wanting to know if a record exists or not, so i return 1 to minimize the return portion of the processing. Not sure that it makes a different really, but it has to be better than '*'

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-25 : 09:06:55

ok, i thought it had greater meaning :)))) that was what i thought also ....

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources