Rank function

[Elden Carnahan]

I am trying to derive rank values in a query, just as one would with the
Rank function in Excel. Can't see how to do this elementary task. Can
anyone advise?

[John Winterbottom]

Re: Rank function

"Elden Carnahan" <eldencarnahan@ comcast.net> wrote in message
news:jJGdnWOmF4-JwUrdRVn-hA@comcast.com. ..[color=blue]
> I am trying to derive rank values in a query, just as one would with the
> Rank function in Excel. Can't see how to do this elementary task. Can
> anyone advise?
>[/color]


This ranks the products in the Northwind products table by price:

select p1.ProductName, UnitPrice,
(
select count(*) from Products p2
where p2.UnitPrice >= p1.UnitPrice
) AS Rank
from Products as p1
order by UnitPrice desc

[elden carnahan]

Re: Rank function


Thanks to the person who replied. I will give that a try.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[almish]

Re: Rank function

"Elden Carnahan" <eldencarnahan@ comcast.net> wrote in message news:<jJGdnWOmF 4-JwUrdRVn-hA@comcast.com> ...[color=blue]
> I am trying to derive rank values in a query, just as one would with the
> Rank function in Excel. Can't see how to do this elementary task. Can
> anyone advise?[/color]

If you search this group with "rank query" you'll find lots of
examples. A simple one here (despite the terrible names used):

Table A:

ID TeamNumber Points
1 1 8
2 2 4
3 3 6

and a query for it

SELECT
(SELECT COUNT(*) FROM [Table A] WHERE Points >= s.Points) AS Rank
, s.ID
, s.TeamNumber
, s.Points
FROM
[Table A] AS s
ORDER BY
s.Points DESC;

' --------------
' John Mishefske
' --------------