Ranking Top 3 Results

T

Tina

I have the following query built in which I am trying to Rank a very simple
1st, 2nd, 3rd (1,2,3) for the records that the query has produced but for
some reason evry record is ranked 1. Can someone please review and let me
know where I am going wrong, what adjustments do I need to make in order to
produce three ranks for each OprName?

SELECT SKYLINE_QUERY_MT.OprID, SKYLINE_QUERY_MT.OprName,
SKYLINE_QUERY_MT.ID, SKYLINE_QUERY_MT.Deliv, (SELECT Count(*)
FROM SKYLINE_QUERY_MT AS X
WHERE X.ID = SKYLINE_QUERY_MT.ID
AND X.Deliv <= SKYLINE_QUERY_MT.Deliv
) AS rank
FROM SKYLINE_QUERY_MT
ORDER BY SKYLINE_QUERY_MT.OprName, SKYLINE_QUERY_MT.ID,
SKYLINE_QUERY_MT.Deliv;
 
T

Tina

My ranking produces "1" for every record. I'm trying to get it to rank 1-2-3
for each record grouped by the operator.
 
K

Ken Snell \(MVP\)

Show us some sample data / records so that we can see what values you're
trying to use.
 
T

Tina

OprID OprName ID Deliv rank
ACN Air Canada 943 2/2/2010 1
ACN Air Canada 949 3/5/2010 1
ACN Air Canada 950 3/5/2010 1
BEJ Air China 824 6/4/2008 1
BEJ Air China 830 7/5/2008 1
BEJ Air China 868 2/1/2009 1
 
Top