July 31, 2009 at 7:48 am
I've a table with results from a run contest.
Let's say tblResults with the following fields:
ParticipantId
Time
I want to produce a list like the following
Name Time Points Rank
Participant1 11:25 50 1
Participant1 11:27 48 2
Participant1 11:28 46 3
Participant1 11:29 45 4
Participant1 11:30 44 5
...
The calculation of points is as follows
The first person gets 50, the second 48, the third 46, and from the fourth person it is only 1 point difference (so fourth person 45 points, 5th person 44 points, ....)
Is it possible with T-SQL to produce a list like that?
July 31, 2009 at 7:55 am
Here is what I did. There is probably a better way but this was the first thing that came to mind.
CREATE TABLE #tblResults (participant_id INT, endTime TIME)
INSERT INTO #tblResults VALUES (1,'11:25')
INSERT INTO #tblResults VALUES (3,'11:26')
INSERT INTO #tblResults VALUES (2,'11:27')
INSERT INTO #tblResults VALUES (4,'11:28')
INSERT INTO #tblResults VALUES (5,'11:29')
INSERT INTO #tblResults VALUES (6,'11:30')
SELECT 'Participant ' + CONVERT(VARCHAR,participant_id),
endTime,
CASE ROW_NUMBER() OVER (ORDER BY endTime)
WHEN 1 THEN 50
WHEN 2 THEN 48
WHEN 3 THEN 46
ELSE 50-((ROW_NUMBER() OVER (ORDER BY endTime))+1)
END AS points,
ROW_NUMBER() OVER (ORDER BY endTime)
FROM #tblResults
July 31, 2009 at 8:59 am
Thanks for the solution.
July 31, 2009 at 6:54 pm
Matt Wilhoite (7/31/2009)
Here is what I did. There is probably a better way but this was the first thing that came to mind.
Nice... the optimizer is also smart enough to know that ROW_NUMBER only get's calculated once even though it appears in the code 3 times.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2009 at 9:07 am
How to avoid negative points
I was trying the following:
CREATE TABLE #tblResults (participant_id INT, endTime TIME)
INSERT INTO #tblResults VALUES (1,'11:25')
INSERT INTO #tblResults VALUES (3,'11:26')
INSERT INTO #tblResults VALUES (2,'11:27')
INSERT INTO #tblResults VALUES (4,'11:28')
INSERT INTO #tblResults VALUES (5,'11:29')
INSERT INTO #tblResults VALUES (6,'11:30')
SELECT 'Participant ' + CONVERT(VARCHAR,participant_id),
endTime,
CASE ROW_NUMBER() OVER (ORDER BY endTime)
WHEN 1 THEN 50
WHEN 2 THEN 48
WHEN 3 THEN 46
WHEN ((50-((ROW_NUMBER() OVER (ORDER BY endTime))+1)) < 0) THEN 0
ELSE 50-((ROW_NUMBER() OVER (ORDER BY endTime))+1)
END AS points,
ROW_NUMBER() OVER (ORDER BY endTime)
FROM #tblResults
DROP table #tblResults
but I'm receiving the following error :
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near '<'.
I was searching for the min function, but this not exist. I was trying the following for the ELSE statement
MIN(0, 50-((ROW_NUMBER() OVER (ORDER BY endTime))+1))
Neither what I'm trying I don't find a solution. Can somebody help?
August 3, 2009 at 9:21 am
There are two variants of the CASE statement
CASE <Expression>
WHEN <Value Expression> THEN <Result Expression>
or you can use
CASE
WHEN <Boolean Expression> THEN <Result Expression>
You are mixing the two variants together, which is why you are getting the error message.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 9:50 am
I understand. So I have to write the following:
CREATE TABLE #tblResults (participant_id INT, endTime TIME)
INSERT INTO #tblResults VALUES (1,'11:25')
INSERT INTO #tblResults VALUES (3,'11:26')
INSERT INTO #tblResults VALUES (2,'11:27')
INSERT INTO #tblResults VALUES (4,'11:28')
INSERT INTO #tblResults VALUES (5,'11:29')
INSERT INTO #tblResults VALUES (6,'11:30')
SELECT 'Participant ' + CONVERT(VARCHAR,participant_id),
endTime,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY endTime) = 1 THEN 50
WHEN ROW_NUMBER() OVER (ORDER BY endTime) = 2 THEN 48
WHEN ROW_NUMBER() OVER (ORDER BY endTime) = 3 THEN 46
WHEN ((50-((ROW_NUMBER() OVER (ORDER BY endTime))+1)) < 0) THEN 0
ELSE 50-((ROW_NUMBER() OVER (ORDER BY endTime))+1)
END AS points,
ROW_NUMBER() OVER (ORDER BY endTime)
FROM #tblResults
DROP table #tblResults
Is there a way to avoid writing multiple times the following:
ROW_NUMBER() OVER (ORDER BY endTime)
If your given it an alias in the select, apparently you can't reuse this further on in the select statement.
CREATE TABLE #tblResults (participant_id INT, endTime TIME)
INSERT INTO #tblResults VALUES (1,'11:25')
INSERT INTO #tblResults VALUES (3,'11:26')
INSERT INTO #tblResults VALUES (2,'11:27')
INSERT INTO #tblResults VALUES (4,'11:28')
INSERT INTO #tblResults VALUES (5,'11:29')
INSERT INTO #tblResults VALUES (6,'11:30')
SELECT ROW_NUMBER() OVER (ORDER BY endTime) as nbr ,'Participant ' + CONVERT(VARCHAR,participant_id),
endTime,
CASE
WHEN nbr = 1 THEN 50
WHEN nbr = 2 THEN 48
WHEN nbr = 3 THEN 46
WHEN ((50-(nbr+1)) < 0) THEN 0
ELSE 50-(nbr+1)
END AS points,
ROW_NUMBER() OVER (ORDER BY endTime)
FROM #tblResults
DROP table #tblResults
You get the following error:
Invalid column name 'nbr'.
Is there a shorter and cleaner way?
August 3, 2009 at 11:08 am
You could do something like this to make it look "cleaner":
CREATE TABLE #tblResults (participant_id INT, endTime TIME)
INSERT INTO #tblResults VALUES (1,'11:25')
INSERT INTO #tblResults VALUES (3,'11:26')
INSERT INTO #tblResults VALUES (2,'11:27')
INSERT INTO #tblResults VALUES (4,'11:28')
INSERT INTO #tblResults VALUES (5,'11:29')
INSERT INTO #tblResults VALUES (6,'11:30')
SELECT CASE
WHEN nbr = 1 THEN 50
WHEN nbr = 2 THEN 48
WHEN nbr = 3 THEN 46
WHEN ((50-(nbr+1)) < 0) THEN 0
ELSE 50-(nbr+1)
END AS points, participant, endTime, nbr
FROM (SELECT ROW_NUMBER() OVER (ORDER BY endTime) as nbr ,
'Participant ' + CONVERT(VARCHAR,participant_id) AS participant,
endTime
FROM #tblResults) temp
DROP table #tblResults
August 3, 2009 at 11:47 am
I believe that you should be using the RANK function rather than ROW_NUMBER, otherwise participants with the same end time may be unfairly awarded a different number of points. In the following I've also simplified the CASE expression slightly by removing the line where ranking = 3 as this case is covered by the following line. Finally, I've moved the RANK() function expression into a CTE rather than use a derived table.
;WITH cteRanking AS (
SELECT participant_id, endTime,
RANK() OVER (ORDER BY endTime) AS ranking
FROM #tblResults
)
SELECT
'Participant ' + CONVERT(varchar(11), participant_id) AS [Name],
endTime AS [Time],
CASE WHEN (ranking = 1) THEN 50
WHEN (ranking = 2) THEN 48
WHEN (ranking BETWEEN 3 AND 48) THEN 49 - ranking
ELSE 0 END AS [Points],
ranking AS [Rank]
FROM cteRanking
ORDER BY ranking
August 5, 2009 at 7:35 am
Thanks. This was really helpful.
Also the solution with CTE is very interesting to avoid writing multiple times
RANK() OVER (ORDER BY endTime) AS ranking
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply