Ranking Top 3 Results

[Guest]

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;

 

[Ken Snell \(MVP\)]

Basic query looks ok. What is not working?

 

[Guest]

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.

 

[Ken Snell \(MVP\)]

What is the data type of Deliv field? What types of values are in it?

 

[Guest]

Deliv field is a date field.

What is the data type of Deliv field? What types of values are in it?

 

[Ken Snell \(MVP\)]

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

 

[Guest]

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

 

[Ken Snell \(MVP\)]

Didn't get chance last night to reply. Will be back in touch soon.

 

[Ken Snell \(MVP\)]

The reason your query doesn't work is because you're using the ID field as a
criterion for the subquery that returns the ranking number. Note that every
record has a different ID value. Therefore, the rank will always be 1 for
every record. Sounds as if you need to use a different field than ID for the
subquery's criterion. Perhaps use OprID instead of ID.