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.
Author |
Topic |
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-07 : 04:04:29
|
Hi,I wrote the query below and everything works fine except that the rank does not display correctly in the case of a tie. The calculated rank after the ties would not be correct due to this code in the query:(SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE H.ScoreTotal > History.ScoreTotal AND L.Gender = Lifter.Gender) AS Rank I found a solution to the problem, in the link below, but I must admit that I have no clue on how to implement that solution in my query to solve the problem. I know that I have to write two small queries that will be used in this larger one.Here is the link to the solution : [url]http://support.microsoft.com/kb/207626/EN-US/[/url]The database can be downloaded from [url]http://www.reitzgh.co.za/add/Database.zip[/url]Make sure to update the MeetDate in the Meet table to reflect the current date before you try to run the query.
SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE H.ScoreTotal > History.ScoreTotal AND L.Gender = Lifter.Gender) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.BodyWeight, Class.Description AS CDescription, Activity.Description AS GDescription, A.Name AS NameA, History.ScoreA, History.SubTotalA, B.Name AS NameB, History.ScoreB, History.SubTotalB, C.Name AS NameC, History.ScoreC, History.SubTotalC, History.ScoreTotal AS ScoreTotalFROM Class, Lifter, Team, History, Meet, Activity, Events A, Events B, Events CWHERE History.ScoreTotal > 0 AND Class.uidClass = Lifter.uidClassRef AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.uidLifter = History.uidLifterRef AND Team.uidMeetRef = Meet.uidMeet AND Lifter.Gender = Activity.Code AND Meet.uidEventsA = A.uidEvents AND Meet.uidEventsB = B.uidEvents AND Meet.uidEventsC = C.uidEvents AND Team.TeamStatus = 0 AND Lifter.LifterStatus = 0 AND (MeetDate = DATE ())GROUP BY History.ScoreTotal, uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight, Class.Description, History.WeightScoreC, Lifter.Gender, Activity.Description, Activity.Activity, Lifter.LifterStatus, A.Name, B.Name, C.Name, History.ScoreA, History.ScoreB, History.ScoreC, History.SubTotalA, History.SubTotalB, History.SubTotalCHAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0)ORDER BY Lifter.Gender ASC, History.ScoreTotal DESC, VAL(Class.Description) DESC, Lifter.BodyWeight ASC |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-07 : 07:38:25
|
Try this "RANK":(SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE (H.ScoreTotal > History.ScoreTotal AND L.Gender = Lifter.Gender)OR(H.ScoreTotal = History.ScoreTotal AND L.Gender = Lifter.Gender AND L.LifterNumber < Lifter.LifterNumber)) AS Rank, I chose LifterNumber as "a corrector", but it can be e.g. BodyWeight or even DateOfBirth. |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-07 : 08:05:03
|
Hi Stoad,Nope it's not working. The query currently resembles something that in short look like this:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 855 EEEE 80I would like to see:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 854 EEEE 80I'm desperate to have someone helping me on the solution given by MSRegards |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-07 : 09:20:51
|
.. means I misunderstood your trouble; try this:(SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE H.ScoreTotal > History.ScoreTotal AND L.Gender = Lifter.GenderandL.uidLifter = (select min(uidLifter) from Lifter LL inner join History HH on LL.uidLifter = HH.uidLifterRef where LL.Gender = L.Gender and HH.scoretotal = H.scoretotal)) AS Rank, |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-10 : 04:09:01
|
Hi Stoad,The code work fine initially until I start to add some more test data. In 2 or more 3 cases the rank is out of sync. Below is the code that I ran to get the results I'm talking about. I've also uploaded a new database with the data I tested on to [url]http://www.reitzgh.co.za/add/Database.zip[/url]SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.WeightScoreA > History.WeightScoreA AND (L.StatusInTeam < 2) AND L.firstname & L.lastname = (SELECT MIN(firstname & lastname) FROM Lifter LL INNER JOIN History HH ON LL.uidLifter = HH.uidLifterRef WHERE HH.WeightScoreA = H.WeightScoreA)) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.BodyWeight, Class.Description AS CDescription, Activity.Description AS GDescription, Events.Name AS EventName, History.WeightScoreA AS ScoreA, History.SubTotalAFROM Class, Lifter, Team, History, Meet, Activity, EventsWHERE (Meet.MeetDate = DATE ()) AND Team.TeamStatus = 0 AND History.WeightScoreA > 0 AND (Lifter.StatusInTeam < 2) AND Class.uidClass = Lifter.uidClassRef AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.uidLifter = History.uidLifterRef AND Team.uidMeetRef = Meet.uidMeet AND Lifter.uidTeamRef = Team.uidTeam AND Lifter.Gender = Activity.Code AND Meet.uidEventsA = Events.uidEventsGROUP BY uidClassRef, Lifter.LifterNumber, Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight, Class.Description, History.WeightScoreA, Lifter.Gender, Activity.Description, Activity.Activity, Events.Name, History.SubTotalA, Lifter.LifterStatus, Lifter.StatusInTeamHAVING (Activity.Activity = 'GenderStatus') AND (Lifter.LifterStatus = 0)ORDER BY Val(Class.Description) ASC, History.WeightScoreA DESC, Lifter.Gender DESC, Lifter.BodyWeight ASC Regards |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-10 : 04:32:28
|
TenT,you missed my "Edited by - Stoad on 01/07/2005 10:22:28".PS Ok, I will check your updated mdb. |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-10 : 08:35:06
|
1.Why the GROUP BY without any aggregation? I think you need no it.And you can safely move HAVING part (its content) into WHERE clause.2.Check this castrated query. It works fine. Why it goes mad after addingtables Class, Team, Meet, Activity, Events I can't say for sure.SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.WeightScoreA > History.WeightScoreA AND L.StatusInTeam < 2ANDL.uidLifter = (select min(LL.uidLifter) from Lifter LL inner join History HH on LL.uidLifter = HH.uidLifterRef whereLL.uidClassRef = Lifter.uidClassRef AND HH.WeightScoreA = H.WeightScoreA)) AS Rank, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname,Lifter.Firstname AS Firstname, History.WeightScoreA AS ScoreA,History.SubTotalAFROM Lifter, HistoryWHERE History.WeightScoreA > 0 AND Lifter.StatusInTeam < 2 AND Lifter.uidLifter = History.uidLifterRef AND Lifter.Gender = 0; Edit: this query returns 51 rows (I count both genders); yours (original) - 31 rows. |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-10 : 09:57:40
|
Hi Stoad, I've changed the code. Thanks. SELECT (SELECT COUNT(*) + 1 FROM History H INNER JOIN Lifter L ON L.uidLifter = H.uidLifterRef WHERE L.uidClassRef = Lifter.uidClassRef AND H.ScoreTotal > History.ScoreTotal AND L.uidLifter = (Select Min(LL.uidLifter) from Lifter LL INNER JOIN History HH on LL.uidLifter = HH.uidLifterRef WHERE LL.uidClassRef = Lifter.uidClassRef AND HH.ScoreTotal = H.ScoreTotal)) AS Rank,Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS CDescription, Activity.Description AS GDescription, A.Name AS NameA, History.ScoreA, History.SubTotalA, B.Name AS NameB, History.ScoreB, History.SubTotalB, C.Name AS NameC, History.ScoreC, History.SubTotalC, History.ScoreTotal AS ScoreTotalFROM Class, Activity, Events AS A, Events AS B, Events AS C,((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef) INNER JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef) INNER JOIN History ON Lifter.uidLifter = History.uidLifterRefWHERE (((History.ScoreTotal)>0) AND ((Lifter.Gender)=[Activity].[Code]) AND ((Class.uidClass)=[Lifter].[uidClassRef]) AND ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND ((Lifter.uidLifter)=[History].[uidLifterRef]) AND ((Team.uidMeetRef)=[Meet].[uidMeet]) AND ((Meet.uidEventsA)=[A].[uidEvents]) AND ((Meet.uidEventsB)=[B].[uidEvents]) AND ((Meet.uidEventsC)=[C].[uidEvents]) AND ((Team.TeamStatus)=0) AND ((Lifter.LifterStatus)=0) AND ((Lifter.StatusInTeam)<2) AND ((Meet.MeetDate)=Date()) AND ((Activity.Activity)='GenderStatus'))ORDER BY Val(Class.Description), Lifter.Gender, History.ScoreTotal DESC; I'll also try this side to look for a fix. Let you know if I find something.Regards |
 |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-10 : 11:06:29
|
This is really easy to do in a report in Access. What are you doing with the results of this query? Further calculations? storing the values? or just outputing the results? if you use a report to output the results, calculating the rank of a row is quite simple using a running total.- Jeff |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-11 : 02:35:40
|
quote: Originally posted by jsmith8858 This is really easy to do in a report in Access. What are you doing with the results of this query? Further calculations? storing the values? or just outputing the results? if you use a report to output the results, calculating the rank of a row is quite simple using a running total.- Jeff
The result will be used in a report. The report is created in VB 6 report writer. Not Crystal. You can create the report in ACCESS though, I believe that I can rebuild it. If VB is not a problem for you I can send you the reports.Seem like you got quite a different angle here. Would you mind to enlight me? |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-11 : 04:34:18
|
TenT,I understood the trouble: we can't calculate Rank on the fly. So, I splitthe whole thing into two queries: the 1st named "q" and the 2nd - "main".Query "q":SELECT Lifter.uidLifter, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS CDescription, Activity.Description AS GDescription, A.Name AS NameA, History.ScoreA, History.SubTotalA, B.Name AS NameB, History.ScoreB, History.SubTotalB, C.Name AS NameC, History.ScoreC, History.SubTotalC, History.ScoreTotal AS ScoreTotalFROM Class, Activity, Events AS A, Events AS B, Events AS C, ((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef) INNER JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef) INNER JOIN History ON Lifter.uidLifter = History.uidLifterRefWHERE (((History.ScoreTotal)>0) AND ((Lifter.Gender)=[Activity].[Code]) AND ((Class.uidClass)=[Lifter].[uidClassRef]) AND ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND ((Lifter.uidLifter)=[History].[uidLifterRef]) AND ((Team.uidMeetRef)=[Meet].[uidMeet]) AND ((Meet.uidEventsA)=[A].[uidEvents]) AND ((Meet.uidEventsB)=[B].[uidEvents]) AND ((Meet.uidEventsC)=[C].[uidEvents]) AND ((Team.TeamStatus)=0) AND ((Lifter.LifterStatus)=0) AND ((Lifter.StatusInTeam)<2) AND ((Meet.MeetDate)=Date()) AND ((Activity.Activity)='GenderStatus')); Query "main" (you can name/ rename both of these queries as you like):SELECT (SELECT COUNT(*) + 1from q t where t.CDescription = q.CDescription and t.GDescription = q.Gdescriptionand t.ScoreTotal > q.ScoreTotal andt.uidLifter = (select min(uidLifter) from q tt where tt.CDescription = t.CDescription and tt.GDescription = t.Gdescription and tt.ScoreTotal = t.ScoreTotal)) AS Rank, q.*FROM qORDER BY Val(CDescription), GDescription, ScoreTotal DESC; |
 |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-11 : 07:29:52
|
In crystal just add a running total formula to your report. set the running total to accumulate the value "1" and have it reset where you need to. Takes about 4 clicks of the mouse! much more efficient than trying to use T-SQL. Access reports has something similiar, add a text box, set it equal to "1", and have it set to "RunningSum".Try reading the help files and/or books on the product you are using.- Jeff |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-11 : 07:37:52
|
quote: Originally posted by jsmith8858 In crystal just add a running total formula to your report. set the running total to accumulate the value "1" and have it reset where you need to. Takes about 4 clicks of the mouse! much more efficient than trying to use T-SQL. Access reports has something similiar, add a text box, set it equal to "1", and have it set to "RunningSum".Try reading the help files and/or books on the product you are using.- Jeff
Hi,This method only works in crystal and unfortunately not in the VB reportwriter.Thanks |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-11 : 08:04:37
|
Hi Stoad,Can you help, two lifters with the same score does not constitute a tie but only the same score and the same bodyweight. Currently the program constitute a tie when the scores are equal.Other than that, what can say, this will help me solve 7 statements and fixing 11 reports.BTW,I was going nuts man, believe me, after a week and a half on the same problem one tend to, I guess you will know from experience.Regards |
 |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-11 : 08:51:30
|
I'm sorry, I misread what you wrote. The VB Report Writer, to speak bluntly, is horrible by the way. - Jeff |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-11 : 09:00:24
|
TenT,try this updated query "main" (staging query "q" stays unchanged):SELECT (SELECT COUNT(*) + 1from q t where t.CDescription = q.CDescription and t.GDescription = q.Gdescriptionand ((t.ScoreTotal > q.ScoreTotal) or (t.ScoreTotal = q.ScoreTotal and t.Bodyweight < q.Bodyweight)) andt.uidLifter = (select min(uidLifter) from q tt where tt.CDescription = t.CDescription and tt.GDescription = t.Gdescription and tt.ScoreTotal = t.ScoreTotaland tt.Bodyweight = t.Bodyweight)) AS Rank, q.*FROM qORDER BY Val(CDescription), GDescription, ScoreTotal DESC , Bodyweight;btw, don't mind to keep asking. |
 |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-01-11 : 09:40:13
|
quote: Originally posted by TenTwenty Hi Stoad,Nope it's not working. The query currently resembles something that in short look like this:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 855 EEEE 80I would like to see:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 854 EEEE 80I'm desperate to have someone helping me on the solution given by MSRegards
By the way -- you know that what you are looking for is to return incorrect results, right? with a tie, the value EEEE is in 5th place, not 4th place. There are 4 items ahead of it, not 3. You are asking for 2+2 to equal something other than 4. That's why you are having so much trouble.- Jeff |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-11 : 09:48:35
|
THAT's IT!Stoad, I only need to display the top 3 lifters in each division seperated by gender, thus, same query but only the top 3to be able to do an award ex.1 = Gold2 = Silver2 = Silver3 = BronzeOr1 = Gold2 = Silver3 = BronzeI might I ask you later to help me with the team positions as this is now all that is left but I need to quickly take a look at my analysis to see how it is done.Thanks! |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-11 : 09:57:32
|
quote: Originally posted by jsmith8858
quote: Originally posted by TenTwenty Hi Stoad,Nope it's not working. The query currently resembles something that in short look like this:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 855 EEEE 80I would like to see:1 AAAA 1002 BBBB 903 CCCC 853 DDDD 854 EEEE 80I'm desperate to have someone helping me on the solution given by MSRegards
By the way -- you know that what you are looking for is to return incorrect results, right? with a tie, the value EEEE is in 5th place, not 4th place. There are 4 items ahead of it, not 3. You are asking for 2+2 to equal something other than 4. That's why you are having so much trouble.- Jeff
Jeff, Yes, True, that's unfortunately the reality I'm sitting with. |
 |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-01-11 : 12:54:08
|
TenTw,it's the easiest part! Just add one more query (I'd call it "SuperMain":):select *from Mainwhere Rank < 4ORDER BY Val(CDescription), GDescription, ScoreTotal DESC, Bodyweight;and remove ORDER BY part from query "Main" - now it's needless there. |
 |
|
TenTwenty
Starting Member
22 Posts |
Posted - 2005-01-12 : 10:55:30
|
Hi Stoad,Thanks for the help again and I'll say a few more times. I spend the whole day on implementing your solution into some of my statements. All VB code is now finished.Look at this weirdo rule, "Point scoring for all World, Continental and Regional Championships shall be : 12, 9, 8, 7, 6, 5, 4, 3, 2, for the first 9placing in any bodyweight category. Thereafter, each lifter who makes a total in the competition shall be awarded one point" The current code is now rendered useless by my dam ignorance. Do you think this can be solved in the statement or should I be looking at something else?Here is the Full code I used, I'll add some more test data tonight.qryDistinctComptTeamSELECT Lifter.uidLifter, Lifter.LifterNumber AS LifterNumber, Lifter.Lastname ASLastname, Lifter.Firstname AS Firstname, Lifter.Bodyweight, Class.Description AS CDescription, Activity.Description AS GDescription, History.ScoreTotal AS ScoreTotal, History.LiftTotal, Team.Name AS TeamName, uidAwardNamesRefFROM Class, Activity, ((Meet INNER JOIN Team ON Meet.uidMeet = Team.uidMeetRef) INNER JOIN Lifter ON Team.uidTeam = Lifter.uidTeamRef) INNER JOIN History ON Lifter.uidLifter = History.uidLifterRefWHERE (((History.ScoreTotal)>0) AND ((Lifter.Gender)=[Activity].[Code]) AND ((Class.uidClass)=[Lifter].[uidClassRef]) AND ((Lifter.uidTeamRef)=[Team].[uidTeam]) AND ((Lifter.uidLifter)=[History].[uidLifterRef]) AND ((Team.uidMeetRef)=[Meet].[uidMeet]) AND ((Team.TeamStatus)=0) AND ((Lifter.LifterStatus)=0) AND ((Lifter.StatusInTeam)<1) AND ((Meet.MeetDate)=Date()) AND ((Activity.Activity)='GenderStatus')); qryIntermComptTeamSELECT (SELECT COUNT(*) + 1FROM qryDistinctComptTeam tWHERE t.CDescription = qryDistinctComptTeam.CDescription AND t.GDescription = qryDistinctComptTeam.Gdescription AND ((t.ScoreTotal > qryDistinctComptTeam.ScoreTotal) OR (t.ScoreTotal = qryDistinctComptTeam.ScoreTotal ANDt.Bodyweight < qryDistinctComptTeam.Bodyweight)) ANDt.uidLifter = (SELECT Min(uidLifter) FROM qryDistinctComptTeam tt WHERE tt.CDescription = t.CDescription AND tt.GDescription = t.Gdescription AND tt.ScoreTotal = t.ScoreTotal AND tt.Bodyweight = t.Bodyweight)) AS Rank, qryDistinctComptTeam.*FROM qryDistinctComptTeam; qryComptTeamSELECT qryIntermComptTeam.*, AwardPoints.Name AS AName, AwardPoints.PointsFROM qryIntermComptTeam, AwardPointsWHERE ((qryIntermComptTeam.Rank) > 0) AND ((AwardPoints.uidAwardNamesRef)=[AwardPoints].[uidAwardNamesRef]) AND((AwardPoints.Rank) = qryIntermComptTeam.Rank)ORDER BY Val(CDescription), GDescription, ScoreTotal DESC , Bodyweight; Regards |
 |
|
Next Page
|
|
|
|
|