[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

RANK

CipherTeKST

3/20/2007 6:37:00 PM

I have a table with 121 rows, 5 columns; last column being a currency value.
How can I rank from the currency value with the max rank being 100, with
every record below 1 ranking as 0. I have tried DENSE_RANK() OVER(Order by
CurrencyValue ASC) but I get the max currency value as 110 and every record
that is 0 in the currency value tied at 1. I need the max currency value to
be 100 and everything below rank 1 to be 0.

Any help will be greatly appreciated, Thanks.
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+
4 Answers

Alejandro Mesa

3/20/2007 7:05:00 PM

0

Can you post some DDL, sample data and expected result?

Thanks,

AMB

"CipherTeKST" wrote:

> I have a table with 121 rows, 5 columns; last column being a currency value.
> How can I rank from the currency value with the max rank being 100, with
> every record below 1 ranking as 0. I have tried DENSE_RANK() OVER(Order by
> CurrencyValue ASC) but I get the max currency value as 110 and every record
> that is 0 in the currency value tied at 1. I need the max currency value to
> be 100 and everything below rank 1 to be 0.
>
> Any help will be greatly appreciated, Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+

CipherTeKST

3/20/2007 8:54:00 PM

0

Well I have a table that looks similar to...

Initials Name Field3 Field4 Money
aaa A aaa aaa 1000.00
bbb B bbb bbb 350.00
ccc C ccc ccc 2500.00
ddd D ddd ddd 5500.00
etc. etc. etc.

But I want to select all these fields with a rank for the 6th field, but
since i have more than 100 records in the table the rank goes higher than
100. Is there a max number we can put on the rank and have everything under 1
to have a rank of 0.

Thanks!
--
CipherTeKST
MCSE: Security 2003, CCNA, Security+


"Alejandro Mesa" wrote:

> Can you post some DDL, sample data and expected result?
>
> Thanks,
>
> AMB
>
> "CipherTeKST" wrote:
>
> > I have a table with 121 rows, 5 columns; last column being a currency value.
> > How can I rank from the currency value with the max rank being 100, with
> > every record below 1 ranking as 0. I have tried DENSE_RANK() OVER(Order by
> > CurrencyValue ASC) but I get the max currency value as 110 and every record
> > that is 0 in the currency value tied at 1. I need the max currency value to
> > be 100 and everything below rank 1 to be 0.
> >
> > Any help will be greatly appreciated, Thanks.
> > --
> > CipherTeKST
> > MCSE: Security 2003, CCNA, Security+

Hugo Kornelis

3/20/2007 11:40:00 PM

0

On Tue, 20 Mar 2007 13:54:13 -0700, CipherTeKST wrote:

>Well I have a table that looks similar to...
>
>Initials Name Field3 Field4 Money
>aaa A aaa aaa 1000.00
>bbb B bbb bbb 350.00
>ccc C ccc ccc 2500.00
>ddd D ddd ddd 5500.00
>etc. etc. etc.
>
>But I want to select all these fields with a rank for the 6th field, but
>since i have more than 100 records in the table the rank goes higher than
>100. Is there a max number we can put on the rank and have everything under 1
>to have a rank of 0.
>
>Thanks!

Hi Ciphertekst,

Since RANK() goes from 1 (best) to infinity, you're probably best of
using someting as (untested):

WITH Ranked
AS
(SELECT Initials, Name, Field3, Field4, [Money],
RANK() OVER (ORDER BY Money DESC) AS rn
FROM YourTable)
SELECT Initials, Name, Field3, Field4, [Money],
CASE WHEN rn > 100 THEN 0 ELSE 101 - rn
FROM Ranked;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

jeevan

3/21/2007 1:19:00 AM

0

What about NTILE() with 100 tiles?

"CipherTeKST" <CipherTeKST@discussions.microsoft.com> wrote in message
news:3383BC46-883F-4759-8957-E7682FD047D0@microsoft.com...
>I have a table with 121 rows, 5 columns; last column being a currency
>value.
> How can I rank from the currency value with the max rank being 100, with
> every record below 1 ranking as 0. I have tried DENSE_RANK() OVER(Order by
> CurrencyValue ASC) but I get the max currency value as 110 and every
> record
> that is 0 in the currency value tied at 1. I need the max currency value
> to
> be 100 and everything below rank 1 to be 0.
>
> Any help will be greatly appreciated, Thanks.
> --
> CipherTeKST
> MCSE: Security 2003, CCNA, Security+