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]

"Elden Carnahan" <el***********@comcast.net> wrote in message
news:jJ********************@comcast.com...

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?

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]

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]

"Elden Carnahan" <el***********@comcast.net> wrote in message news:<jJ********************@comcast.com>...

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?

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
' --------------