Help with a Query -- sql server programming

Steve Kass
1/2/2004 9:04:01 PM

Your example and your explanation don't seem to match.

You say you count only the team with the highest points. That is team
#1, no matter how you count it, as the team's total points
(121221+3224+2454+2646+2963+2262 = 134770 vs. 13541, or the highest
individual score on that team (121221 vs. 13541), or the team's points
for the specific login_ID (121221 vs. 13541).
But you say you want the 13541 points, which is from team #2. Can you
please explain again what results you want?

Please show the complete results you want, and explain what you mean by
"only the team with the highest points is counted for the logins"

SK

[quoted text, click to view]

Rollin 4 Eva
1/3/2004 11:33:03 AM

I'm sorry about that. Guess I wasn't clear. The results I'm trying to get
is this:

login_ID race_ID team_ID TotalPoints
11 1 2 13541
12 1 1 3224
1318 1 1 2963
23 1 1 2646
13 1 1 2454
1319 1 1 2262

login_ID "11" has 2 teams playing in the race but I only want to grab the
team with the highest TotalPoints. Any given login_ID can have anywhere
from 5 to 10 teams but only the team with the highest score is counted in
the query. So in the table above, each different team for each login, will
have its own row. So, essentially, the query I'm trying to achieve will
have distinct login_IDs. Yeah I guess I want the highest teams TotalPoints
for the specific login_ID. Thanks for your help :)

Rollin

[quoted text, click to view]

Rollin 4 Eva
1/3/2004 12:31:17 PM

Oops. That was a huge mistake on my part and I understand why you were
confused. There's an extra 1 on the login_ID 11 for team 1. The table
should look like this:
login_ID race_ID team_ID TotalPoints
11 1 1 12122
12 1 1 3224
13 1 1 2454
23 1 1 2646
1318 1 1 2963
1319 1 1 2262
11 1 2 13541

But yeah Steve, I think this will work. I guess we can solve the tie
problem by adding a DISTINCT after that first select heh? Since in the
event of tie, we're only looking for one of the scores.
I've changed the syntax of my stored procedure to match what you gave and it
looks like it works, but for some reason, the query results went from a
count of 1812 (which is what it should be) to 134:

CREATE PROCEDURE stor_LoginRacePoint_select_finish_order
@division_ID int,
@shootout_ID int AS
SELECT DISTINCT tlrp.login_ID, LoginName, TotalPoints, FirstName + ' ' +
LastName AS CustomerName
FROM tblLoginRacePoint tlrp
INNER JOIN tblLoginShootout tls ON tlrp.login_ID = tls.login_ID
INNER JOIN tblLogin tl ON tlrp.login_ID = tl.login_ID
INNER JOIN tblCustomer tc ON tl.customer_ID = tc.customer_ID
WHERE (tls.division_ID = @division_ID) AND (tlrp.race_ID IN (SELECT race_ID
FROM tblShootoutRace WHERE shootout_ID = @shootout_ID)) AND (NOT EXISTS
(SELECT * FROM tblLoginRacePoint tlrp2 WHERE tlrp2.login_ID = tlrp.login_ID
AND tlrp2.TotalPoints > tlrp.TotalPoints))
ORDER BY TotalPoints DESC
GO

Any ideas?

Thanks again
Rollin

[quoted text, click to view]

Steve Kass
1/3/2004 12:51:22 PM

Rollin,

I still don't understand why you want login_ID=11, team_ID=2 in your
answer. Team_ID 2 does not have the highest score for login_ID=11.

If you do want for each login_ID, only the row with that login_ID's
highest scoring team, you can do this:

select
login_ID,
race_ID,
team_ID,
TotalPoints
from yourTable T1
where not exists (
select * from yourTable T2
where T2.login_ID = T1.login_ID
and T2.TotalPoints > T1.TotalPoints
)

You will not get the results you show here. You will get team_ID 1 for
login_ID 11 instead, because it has higher total points.

This query also has no rules for how to break a tie, if two team_ID's
for the same login are tied for highest points, and it does not address
what should happen if there is more than one race represented in the
table or if and if (login_ID, team_ID) is not unique.

SK

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Help with a Query