How to use sequential numbers to sort different fields in a Query?

[ADezii]

Thanks to Rabbit, I am very, very, close to a solution. I'll post the SQL and an Attachment. The problem with Ranking occurs when Times are exactly the same, as indicated by the Rankings for 8:05:00 PM in the Attached Database. If no 2 Times are exactly the same, the SQL works perfectly.

SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time)+1) 
AS OverallPosition, 
T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1) 
AS GenderPosition, 
Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
AS CategoryPosition
FROM resultsdata AS T1;

P.S. - Surprised I got this far, going to bed and will try to figure out the last remaining detail tomorrow, unless one of you guys beats me to the punch.

[Jerry Winston]

You can usually make use of RANK BY to generate these kinds of sequential numbers.

Try this:

DECLARE @resultsdata TABLE(
	[ID] [int] NOT NULL,
	[firstname] [nvarchar](255) NULL,
	[surname] [nvarchar](255) NULL,
	[Time] [nvarchar](255) NULL,
	[OverallPosition] [nvarchar](255) NULL,
	[gender] [nvarchar](255) NULL,
	[GenderPosition] [nvarchar](255) NULL,
	[Category] [nvarchar](255) NULL,
	[CategoryPosition] [nvarchar](255) NULL
)

INSERT INTO @resultsdata VALUES (1,'Anny','August','17:16',NULL,'female',NULL,'Senior',NULL)
INSERT INTO @resultsdata VALUES (2,'John','Wrong','18:06',NULL,'male',NULL,'Senior',NULL)
INSERT INTO @resultsdata VALUES (3,'John','White','18:25',NULL,'Male',NULL,'Senior',NULL)
INSERT INTO @resultsdata VALUES (4,'Susan','Black','19:08',NULL,'Female',NULL,'senior',NULL)
INSERT INTO @resultsdata VALUES (5,'John','July','19:21',NULL,'male',NULL,'senior',NULL)
INSERT INTO @resultsdata VALUES (6,'John','September','19:56',NULL,'male',NULL,'junior',NULL)
INSERT INTO @resultsdata VALUES (7,'Peter','Right','20:05',NULL,'male',NULL,'Junior',NULL)
INSERT INTO @resultsdata VALUES (8,'Peter','Water','20:05',NULL,'male',NULL,'junior',NULL)
INSERT INTO @resultsdata VALUES (9,'Susan','April','21:45',NULL,'Female',NULL,'junior',NULL)
INSERT INTO @resultsdata VALUES (10,'Diane','Coke','21:56',NULL,'female',NULL,'junior',NULL)



SELECT ID,firstname,surname,[Time],
rank() over (partition by 'x' order by [Time])[OverallPosition], 
gender,
rank() OVER (PARTITION BY gender order by [Time]) [GenderPosition],
category,
rank() over (partition by Category order by [Time]) [CategoryPosition]
FROM @resultsdata
ORDER BY [Time] ASC

Sorry this code is for SQL Server!

[ADezii]

@Rabbit - I am really pulling my hair out on this one, and would desperately appreciate you assistance. This scenario is particularly funny, since I am bald! For the life of me, I cannot figure how to break a Tie for 'exact' Time Matches, and adjust Rankings accordingly. Thanks to your advice, I have gotten this far, but now I am up against a brick wall. The OP and I have a lot of time invested in this Project, and would hate to have it abruptly end here. Kindly download the Attachment and refer to IDs 7 and 8. As always, thanks.

[Jerry Winston]

@ADezii

Do you have a rule that explains why one runner should be ranked above another if they have the same time?

In any case this might be your solution (look at the overallPosition field):

SELECT T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) )) 
AS OverallPosition, 
T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1) 
AS GenderPosition, 
Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
AS CategoryPosition
FROM resultsdata AS T1;

[ADezii]

I actually tried similar Logic with erroneous Results as listed below. I restricted the Output to only Time and Overall Position:

SELECT T1.Time, ((SELECT Count(*)  FROM  resultsdata
 WHERE resultsdata.Time < T1.Time OR (resultsdata.ID=T1.ID OR resultsdata.ID<T1.ID) ))
 AS OverallPosition
FROM resultsdata AS T1
ORDER BY T1.Time;

Query OUTPUT:

Time	          OverallPosition
5 :16:00 PM	        1
6 :06:00 PM	        2
6 :25:00 PM	        3
6 :30:03 PM	       11
7 :08:00 PM	        5
7 :17:02 PM	       13
7 :21:00 PM	        7
7 :56:00 PM	        8
8 :05:00 PM	       10
8 :05:00 PM	        9
9 :45:00 PM	       11
9 :55:55 PM	       13
9 :56:00 PM	       13

Results using current Logic, excluding First and Last Names. Notice the equal Ranking for match on Time Slot of 8:05:00 PM for all Position Ranks(3) - Lines 10 and 11.

Time	OverallPosition	gender	GenderPosition	Category	CategoryPosition
5 :16:00 PM	1	       Female	      1	        Senior	      1
6 :06:00 PM	2	       Male	        1	        Senior	      2
6 :25:00 PM	3	       Male	        2	        Senior	      3
6 :30:03 PM	4	       Male	        3	        Senior	      4
7 :08:00 PM	5	       Female	      2	        Senior	      5
7 :17:02 PM	6	       Male	        4	        Junior	      1
7 :21:00 PM	7	       Male	        5	        Senior	      6
7 :56:00 PM	8	       Male	        6	        Junior	      2
8 :05:00 PM	9	       Male	        7	        Junior	      3
8 :05:00 PM	9	       Male	        7	        Junior	      3
9 :45:00 PM	11	      Female	      3	        Junior	      5
9 :55:55 PM	12	      Female	      4	        Junior	      6
9 :56:00 PM	13	      Female	      5	        Junior	      7

[Jerry Winston]

There are only two scenarios in which we want to COUNT the records "above" the current row for T1. When the time is less than the current row. and when the time is equal to the current row but the ID is less than T1 row.



Here's the corrected code:

SELECT T1.Time, ((SELECT Count(*)  FROM  resultsdata
 WHERE resultsdata.Time < T1.Time OR (resultsdata.Time=T1.Time AND resultsdata.ID < T1.ID) )+1)
 AS OverallPosition
FROM resultsdata AS T1
ORDER BY T1.Time;

[Rabbit]

If they tie, shouldn't they have the same rank? Won't the runners be angry if they're arbitrarily ranked lower than someone they tied with?

[Jerry Winston]

@Rabbit

I agree. (That's why I asked about tie-break rules)
But it doesn't seem to be part of ADezii's requirement.

[ADezii]

@Jerry and Rabbit: First of all, thanks a million for your help on this puzzling matter. As far as Ties go, this question should actually be directed to the Original Poster of this Thread, namely: neelsfer. He must define exactly what the Tie Breaker is in the event of equal Time Finishes, and this Tie Breaker must be Unique as I see it (cannot be a Race Registration Date). I know that we were trying to incorporate Milliseconds into the Timings in a Related Thread, probably for this exact scenario, but this is not that simple as Milliseconds are not integrated into Access Date/Time Fields. I'm too deeply into this, which is why I need some outside opinions. Do either one of you disagree with anything that was previously stated in this Post?

@neelsfer - Need you intervention at this point on the matter of Ties.

[Jerry Winston]

@ADezii

Sorry, I did definitely pointed the tie-beak question at you instead of OP.

[ADezii]

Not a problem, Jerry. The OP and I are virtually one and the same at this point! (LOL). I was actually thinking of making a [Milliseconds] Field {INTEGER} as the Tie Breaker. This Field would exist independently of the [Time] Field as a matter of necessity. The odds of 2 runners finishing at exactly the same Hour, Minute, Second, and Millisecond would be extremely small, but again we'll just have to wait for the OP. Thanks again.

[neelsfer]

Thxs guys. If i time a mountain biking event, then the chances are almost zero to have a tie as we capture the racenumbers as they pass the finish line.

The problem comes in with Road cycling (thats now what Mr "drugfree" Armstrong does).
They make use of what is called a "bunch or group time". If a group of riders all finish TOGETHER, they are all given the same finish time, but we manually identify the first 3 riders usually for prizes.

It could sometimes be 5-20 riders. The finish sequence is then 1,2,3,4,5,6...1 0, as we stop them in a "shoot" in their group finish sequence, but the same time is given to everybody then.
I use a button in my program that saves that bunch time in a textbox, and then i transfer that finish time to where the subform data is captured, and i just add the racenumbers afterwards.

In downhill mountain biking ( is going like a maniac down a mountain with no brakes) the riders start and finish on their own, and you may sometimes have a tie in seconds. This is where milliseconds may help.
Almost 90% of the races i do is normal mountainbiking, so lets rather assume there are no two riders with the same time in this report..
Hope that helps

[ADezii]

If such is the case, then

SELECT T1.ID, T1.firstname, T1.surname, T1.Time, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time < T1.Time)+1) 
AS OverallPosition, T1.gender, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.gender = T1.gender)+1)
 AS GenderPosition, T1.Category, ((SELECT Count(*)  FROM  resultsdata WHERE resultsdata.Time< T1.Time AND resultsdata.Category = T1.Category)+1) 
AS CategoryPosition
FROM resultsdata AS T1
ORDER BY T1.Time;

should do the trick.