rank to percentile question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

rank to percentile question

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-25 : 18:53:21

Hi there

I have the following scenario


declare @ranky table(BeverageType varchar(50), BeverageName varchar(50),original_date datetime)
Insert into @ranky
Select 'Beer', 'Frothy', getdate()
UNION ALL
Select 'Beer', 'Big Up', getdate() -1
UNION ALL
Select 'Beer', 'Elephant', getdate() -2
UNION ALL
Select 'Beer', 'Tiger', getdate() -3
UNION ALL
Select 'Beer', 'Chin up', getdate() -4 

SELECT BeverageType,   
       BeverageName,
       original_date ,   
       RANK() OVER (PARTITION BY BeverageType ORDER BY original_date) AS 'RANK' ,
       100.0E * RANK() OVER (PARTITION BY BeverageType ORDER BY original_date)/ COUNT(*) OVER () As 'Match %'
 FROM @ranky

But as you notice rank 1 has lowest percentile, I want the percentile to be descending for the rank. So rank 1 should be 100%, rank 2 80% and so on. What am I doing wrong here?

Thanks

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-25 : 19:15:11

this does it but don't know why


declare @ranky table(BeverageType varchar(50), BeverageName varchar(50),original_date datetime)
Insert into @ranky
Select 'Beer', 'Frothy', getdate()
UNION ALL
Select 'Beer', 'Big Up', getdate() -1
UNION ALL
Select 'Beer', 'Elephant', getdate() -2
UNION ALL
Select 'Beer', 'Tiger', getdate() -3
UNION ALL
Select 'Beer', 'Chin up', getdate() -4 

SELECT * FROM 
  (
		SELECT BeverageType   
			   ,BeverageName
			   ,original_date    
			   --,RANK() OVER (PARTITION BY BeverageType ORDER BY original_date DESC) AS 'RANK' 
			   ,100.0E * RANK() OVER (PARTITION BY BeverageType ORDER BY original_date DESC)/ COUNT(*) OVER () As 'Match %'
		 FROM @ranky
		) a
ORDER BY 4 desc

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-25 : 19:17:31

this too shall work


		SELECT BeverageType   
			   ,BeverageName
			   ,original_date    
			   ,RANK() OVER (PARTITION BY BeverageType ORDER BY original_date ) AS 'RANK' 
			   ,100.0E * RANK() OVER (PARTITION BY BeverageType ORDER BY original_date DESC)/ COUNT(*) OVER () As 'Match %'
		 FROM @ranky
        ORDER BY 3

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources