Derive Ranking Function

[robboll]

I am trying to create a function that will take two columns of data
such as this:

field1 field2
d 977
a 87
c 768
b 756
b 754
a 75
d 743
c 686
e 67
a 655
a 565
b 54
c 54
e 456
c 445
c 445
e 378
d 333

And give me a result like this:

field1 field2 FunctionResult:
a 75 1
a 87 2
a 565 3
a 655 4
b 54 1
b 754 2
b 756 3
c 54 1
c 445 2
c 446 3
c 686 4
c 768 5
d 333 1
d 743 2
d 977 3
e 67 1
e 378 2
e 456 3

Notice the function result ranks the numberic column based on the
variable. Any function tips greatly appreciated!

RBollinger

[pietlinden@hotmail.com]

Re: Derive Ranking Function

Found lots of answers in the NG, but none seem to work right. They're
all variations on this:

SELECT p1.ProductName, p1.UnitPrice, (select count(*) from Products p2
where p2.UnitPrice >= p1.UnitPrice) AS Rank
FROM Products AS p1
ORDER BY p1.UnitPrice DESC;


but the rankings are a bit odd... they'll evaluate ties kind of weird
thing rank
a 2
c 2
s 3
b 4

doing this in code would work, I think, but I would rather not go
there... any ideas anybody how to get this to work so you get a
numbering like
1,1, 3, 4? (tie for first, so no second place?)

[lesperancer@natpro.com]

Re: Derive Ranking Function

try this query
SELECT p1.Field1, p1.Field2, (select count(*) from tblRanking p2
where p2.field1 = p1.field1 and p2.field2 <= p1.field2) AS Rank
FROM tblRanking AS p1
ORDER BY p1.Field1, p1.Field2;