Statistical Queries - 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

SQL Server Development (2000)

Statistical Queries

Author

Topic

cscragg
Starting Member

2 Posts

Posted - 2006-11-12 : 10:02:41

I have what seems to be a rather straight forward question I need to answer with a query, however, either I am making this too difficult or I have overlooked a key TQSL function.

I have a user defined function that you pass a key into, in this case a user id. The result set are tests, grouped by test number, associated with that user id.

UserId TestNo Score Date Location
-------------------------------------------------
12345 1 45 10/10/2006 Atlanta
12345 1 36 10/1/2006 Knoxville
12345 2 52 9/12/2005 New York
12345 2 22 7/21/2004 Dallas
12345 3 48 10/21/2003 Atlanta
12345 3 43 10/19/2003 Atlanta
12345 4 52 6/18/2002 Knoxville
12345 4 50 4/26/2002 Charlotte

In this example, I've limited the number of fields that are returned, but the actual function returns roughly 20 fields.

The question I am trying to answer is, "Return the entire row for each test that has the highest score."

I have approached this stored procedure with using MAX functions, CUBE, ROLLUP and just about everything else I can think of in trying to get to the answer. I have been unsuccessful to date. Below is the result I'd like to return:

UserId TestNo Score Date Location
-------------------------------------------------
12345 1 45 10/10/2006 Atlanta
12345 2 52 9/12/2005 New York
12345 3 48 10/21/2003 Atlanta
12345 4 52 6/18/2002 Knoxville

So, I'm not sure if I'm over complicating what I am trying to achieve, but what I have so far is not delivering the desired result. I'd like some fresh eyes to look at this issue and recommend some ways to solve this problem. I have tunnel vision at this point.

Thanks.
Chris

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-11-12 : 10:13:22

[code]
select UserId TestNo Score Date Location
from table t
where Score = (select max(Score) from table x where x.TestNo = t.TestNo)

or

select UserId TestNo Score Date Location
from table t
inner join
(
select TestNo, max_Score = max(Score)
from table
group by TestNo
) m
on t.TestNo = m.TestNo
and t.Score = m.max_Score

[/code]

cscragg
Starting Member

2 Posts

Posted - 2006-11-13 : 09:31:06

Worked well. Thanks for your help.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources