Trying to rank the 0.000 data as well

Question

Post reply

Trying to rank the 0.000 data as well

tinamiller1

SSC-Addicted

Points: 496
More actions
December 20, 2016 at 11:38 am

#333077

I have a table of 33k rows and I have no issues ranking where there are values over 0.000, it is the values that are 0.000 that I have a problem with and not sure how to account for them:
This is the code I am using:
<code>
/*ranking*/
select compmcd.hedismeasure, compmcd.measureid, compmcd.competitor, compmcd.market, compmcd.prodabbrev, compmcd.hedisrate, (select count(*)+1
from anthemq.dbo.compmcd as t2
where t2.hedisrate > compmcd.hedisrate AND t2.measureid = compmcd.measureid and t2.market = compmcd.market and t2.prodabbrev = compmcd.prodabbrev) as rank into comprank
from compmcd where hedisrate is not null
order by compmcd.measureid desc , compmcd.market, compmcd.prodabbrev, compmcd.hedisrate desc;
</code>
When I run this I get this back what is in the attachment on the first set of rows but what I need is what is on the lower portion of rows.
I know it is because the code is looking at the maximum values and because there are multiple entities that have 0.000 it gives them all the same rank because it does not know how to rank it. But I am not sure how to account for that other than to go through and do like 0.0001, 0.0002 for anyone that has a 0.0000 so I can get a correct rank

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 1

You've got a much better chance of getting an answer if you were to post consumable data.

Please read Forum Etiquette: How to post data/code on a forum to get the best help[/url]

and post accordingly.

tinamiller1 SSC-Addicted Points: 496 More actions · Answer 2

I am not sure I understand how to get the information into a blue screen like what you have in the instructions.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Hi and welcome so SSC. For starters here is that query with some formatting so it is easier to read. You can add format boxes like this by using the IFCode shortcuts (they are on the left side when posting).

select compmcd.hedismeasure

, compmcd.measureid

, compmcd.competitor

, compmcd.market

, compmcd.prodabbrev

, compmcd.hedisrate

,

(

select count(*)+1

from anthemq.dbo.compmcd as t2

where t2.hedisrate > compmcd.hedisrate

AND t2.measureid = compmcd.measureid

and t2.market = compmcd.market

and t2.prodabbrev = compmcd.prodabbrev

) as rank into comprank

from compmcd

where hedisrate is not null

order by compmcd.measureid desc

, compmcd.market

, compmcd.prodabbrev

, compmcd.hedisrate desc;

For the actual problem at hand we can't really help you because we have no idea what your data is like or what you are trying to do. We would need to have the ddl (create table statement), sample data (insert statements) and desired output based on the sample data.

Please see the first article referenced in my signature for best practices when posting.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sue_H SSC Guru Points: 90860 More actions · Answer 4

When you do a reply, look on the left for the IFCode Shortcuts.

When you post SQL statements, click on the one with bracket code=sql

and you paste your SQL statements between the two tags - the code=sql and /code tags with brackets.

There is a test forum if you want to practice first

http://www.sqlservercentral.com/Forums/Forum1084-1.aspx

Sue

drew.allen SSC Guru Points: 76990 More actions · Answer 5

You're using a subquery to get the rank when you should be using ROW_NUMBER().

/*ranking*/

SELECT compmcd.hedismeasure,

compmcd.measureid,

compmcd.competitor,

compmcd.market,

compmcd.prodabbrev,

compmcd.hedisrate,

ROW_NUMBER() OVER(PARTITION BY measureid, market, prodabbrev ORDER BY hedisrate DESC, measureid DESC ) AS rn

FROM compmcd WHERE hedisrate IS NOT NULL

ORDER BY compmcd.measureid DESC , compmcd.market, compmcd.prodabbrev, compmcd.hedisrate DESC;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA