Ranking in stored procedure

Question

Ranking in stored procedure

jolan.mahinay

Ten Centuries

Points: 1111
More actions
July 29, 2010 at 2:25 am

#235853

hello to all
i would like to ask help if how i am going to write this on the stored proc about ranking base on result like for example the result for query should be like this..
Name Result Rank
Joan 5 1
Jhon 5 1
kris 3 2
alice 2 3
boyet 2 3
auh 1 4
helen 0 5
and soon....
how can i achieve that dynamically..please help thanks in advance..

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Look up DENSE_RANK() in BOL (Books On-line).

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 2

Waiting for the kettle to boil, so I have a minute to spare...

CREATE TABLE dbo.Test

(

Name VARCHAR(10) NOT NULL,

Result INTEGER NOT NULL,

);

GO

CREATE VIEW dbo.TestView

WITH SCHEMABINDING

AS

SELECT Name,

Result,

[Rank] = DENSE_RANK() OVER (

ORDER BY Result)

FROM dbo.Test;

INSERT dbo.Test (Name, Result)

VALUES ('Joan', 5),

('John', 5),

('Kris', 3),

('Alice', 2),

('Boyet', 2),

('Auh', 1),

('Helen', 0);

GO

SELECT *

FROM dbo.TestView

ORDER BY

[Rank];

GO

DROP VIEW dbo.TestView;

DROP TABLE dbo.Test;

See this link: DENSE_RANK

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 3

i read the book and its good but the sample there does not meet my expectation, i dont even see where could be the columns that will provide for ranking, all i wanted to know is how i am going to rank my result to designated level, i tried already the row_number() over() but it does not work i have two columns which are Name and Result and other is rank but is not a column on the table its only on case statement but they will never survive if number or level on case statement will change, so i want it to happen dynamically which is ever is the higher result should be Rank 1 and soon..please help..thanks a lot for providing that link..

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 4

HELLO SIR I TRIED TO RUN YOUR QUERY BUT IT HAS AN ERROR.. THIS ARE THE ERROR ON THAT QUERY

Msg 156, Level 15, State 1, Procedure TestView, Line 10

Incorrect syntax near the keyword 'INSERT'.

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dbo.TestView'.

Msg 3701, Level 11, State 5, Line 1

Cannot drop the view 'dbo.TestView', because it does not exist or you do not have permission.

Msg 3701, Level 11, State 5, Line 2

Cannot drop the table 'dbo.Test', because it does not exist or you do not have permission.

Paul White SSC Guru Points: 150467 More actions · Answer 5

jolan.mahinay (7/29/2010)
HELLO SIR I TRIED TO RUN YOUR QUERY BUT IT HAS AN ERROR.. THIS ARE THE ERROR ON THAT QUERY

No error. It is written for SQL Server 2008, which is the forum you posted your question in.

Add your own sample data using syntax valid in whatever version you are actually using.

When posting your next question, please choose the appropriate forum.

Thanks!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

jolan.mahinay Ten Centuries Points: 1111 More actions · Answer 6

THANKS MR PAUL I JUST REVISED MY SCRIPT USING THE DENSE_RANK() AND I GOT THE RANKING BUT I WILL OBSERVE THIS FIRST IF THIS WILL CONTINUE ON A CORRECT RANKING ANYWAY HERE'S MY REVISED SCRIPT..

ALTER PROC [dbo].[CIMS_VOTE_ELECTIONRESULT_SELECT1A1B]

AS

create table #table

(

[Candidates][varchar](256)null,

[location][varchar](10)null,

[Result][int] not null default(0),

)

create table #table1

(

[Candidates][varchar](256)null,

[location][varchar](10)null,

[Result][int] not null default(0),

)

INSERT INTO #table

SELECT

upper(cm.fclastname)+', '+ upper(cm.fcfirstname) + ' '+ upper(cm.fcmiddlename) as candidates,

pc.pk_PRollCode as location,

count(A.fcCandidateID) as totalvotes

FROM dbo.CIMS_Vote_CandidateMaster cm

LEFT join dbo.CIMS_Vote_PositionMaster pm

ON cm.fcNominatedfor = pm.fcPositionID

LEFT join dbo.CIMS_m_Member_PayrollCode pc

ON pm.fcLocation = pc.pk_PRollID

LEFT join dbo.CIMS_Vote_StorageGrid1A A

ON cm.CandidateID = A.fcCandidateID

where A.fcYearVote =YEAR(GETDATE())

group by cm.fcnominatedfor,

cm.fclastname,

cm.fcfirstname,

cm.fcmiddlename,

pc.pk_PRollCode

ORDER BY count(A.fcCandidateID) DESC

INSERT INTO #table1

SELECT

upper(cm.fclastname)+', '+ upper(cm.fcfirstname) + ' '+ upper(cm.fcmiddlename) as candidates,

pc.pk_PRollCode as location,

count(B.fcCandidateID) as totalvotes

FROM dbo.CIMS_Vote_CandidateMaster cm

LEFT join dbo.CIMS_Vote_PositionMaster pm

ON cm.fcNominatedfor = pm.fcPositionID

LEFT join dbo.CIMS_m_Member_PayrollCode pc

ON pm.fcLocation = pc.pk_PRollID

LEFT join dbo.CIMS_Vote_StorageGrid1B B

ON cm.CandidateID = B.fcCandidateID

where B.fcYearVote =YEAR(GETDATE())

group by cm.fcnominatedfor,

cm.fclastname,

cm.fcfirstname,

cm.fcmiddlename,

pc.pk_PRollCode

ORDER BY count(B.fcCandidateID) DESC

SELECT

Candidates,

Location,

Result,

Rank = DENSE_RANK()OVER(ORDER BY Result DESC)

FROM #table

UNION ALL

SELECT

Candidates,

Location,

Result,

Rank = DENSE_RANK()OVER(ORDER BY Result DESC)

FROM #table1

DROP TABLE #table

DROP TABLE #table1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

CREATE TABLE #SampleData (Name VARCHAR(10), Result INT)

INSERT INTO #SampleData (Name, Result)

SELECT 'Joan', 5 UNION ALL

SELECT 'Jhon', 5 UNION ALL

SELECT 'kris', 3 UNION ALL

SELECT 'alice', 2 UNION ALL

SELECT 'boyet', 2 UNION ALL

SELECT 'auh', 1 UNION ALL

SELECT 'helen', 0

SELECT Name,

Result,

ResultRank = RANK() OVER(ORDER BY Result DESC),

ResultDenseRank = DENSE_RANK() OVER(ORDER BY Result DESC)

FROM #SampleData

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 8

Thank you Chris, that was very kind. 😎

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Paul White NZ (7/29/2010)
Thank you Chris, that was very kind. 😎

No worries Paul I could see you were in deep water there, mate 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 10

Chris Morris-439714 (7/29/2010)
No worries Paul I could see you were in deep water there, mate 😀

:laugh: :laugh: :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi