T-sql Syntax help needed

Question

Post reply

T-sql Syntax help needed

mw_sql_developer

SSCoach

Points: 19445

May 13, 2020 at 7:09 pm

#3751294

Question: I want to create a sequential rank.

Run the code and you will understand what I mean. I need baby5 and baby6 get a 2 and so on.

The idea is if we have 8 people with 1oo score all of them get a 1

Then the 2 folks with 98 get a 2

97 gets a 3

95 gets a 4 and so on.

How can I get that ! Thanks in advance.

if object_id('tempdb..#t') is not null drop table #t;

create table #t(name varchar(100), score int );

INSERT INTO #t(name, score ) values ( 'baby1', 100 );
INSERT INTO #t(name, score ) values ( 'baby2', 100 );
INSERT INTO #t(name, score ) values ( 'baby3', 100 );
INSERT INTO #t(name, score ) values ( 'baby4', 100 );
INSERT INTO #t(name, score ) values ( 'baby5', 98 );
INSERT INTO #t(name, score ) values ( 'baby6', 98 );
INSERT INTO #t(name, score ) values ( 'baby7', 97 );
INSERT INTO #t(name, score ) values ( 'baby8', 95 );
INSERT INTO #t(name, score ) values ( 'baby9', 95 );
INSERT INTO #t(name, score ) values ( 'baby910', 85 );
INSERT INTO #t(name, score ) values ( 'baby911', 83 );
INSERT INTO #t(name, score ) values ( 'baby912', 83 );
INSERT INTO #t(name, score ) values ( 'baby913', 80 );

;
With a as
( 
Select  
name
,RANK() OVER (  order by score desc ) as RNK  
from #t 
)
Select * from a

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Try using DENSE_RANK instead of RANK

With a as
( 
Select  
name, score
,DENSE_RANK() OVER (  order by score desc ) as RNK  
from #t 
)
Select * from a

Sue

MiraAniston Newbie Points: 2 More actions · Answer 2

I tried and it didn’t work for me either. Maybe you have some other solutions? Thanks

Visit my website https://prohighgrades.com/dissertation-help

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

MiraAniston wrote:

I tried and it didn’t work for me either. Maybe you have some other solutions? Thanks

Saying something as nebulous as "it didn't work" without telling us WHAT about it didn't work doesn't help us help you a bit.

I tested the code Sue provided with her simple substitution of DENSE_RANK for RANK and it worked just fine. Since there's no apparent need from what you posted for the CTE, I removed the CTE and it also works just fine.

Here's the code...

 SELECT  name, score
        ,RNK = DENSE_RANK() OVER (ORDER BY score DESC )
   FROM #t
;

... and here are the results I got, which are also exactly the same as what Sue's code provided...

Now, WHAT about the code doesn't actually work for you? 😉 Are you actually using SQL Server or are you using something else?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)