Rank from lowest to highest

[stapuff]

I have a query that I want to rank from lowest to highest based on a group (Ord)

The follow sql ranks it highest to lowest

Rank: (SELECT Count(*) FROM ZPD_Count1 As U WHERE [Rate] > [ZPD_Count1].[Rate] AND U.Ord = ZPD_Count1.Ord)+1

Returns this result

Ord Rate Rank
123456 425.36 3
123456 725.25 2
123456 895.62 1

I want it to return

Ord Rate Rank
123456 425.36 1
123456 725.25 2
123456 895.62 3

Regardless of what I try I can not return the rank to what I want it.

The best I got it to do was the following but it always skipped 1

Ord Rate Rank
123456 425.36 2
123456 725.25 3
123456 895.62 4


Any suggestions would be greatly appreciated.


stapuff

 

[xenou]

Query the query:

Select * FROM Query1 ORDER BY Rank DESC;

Where Query1 is your saved query. (though I wonder if you couldn't put an order by in the original query ... you are asking to order your results, so I am surprised to see that there is no order by clause there).

 

[stapuff]

xenou -

I appreciate your post.

My saved query has a Ordered by, which did not help, and I did add an Ordered By to this which also did not. I do not get what is going on so that's why I posted the request for help.

Stapuff

 

[xenou]

Can you post your entire query? What you posted looks to me like just one field from a query. It's hard to really see what's going on here. In any case, there's no order by in your posted query so I don't see how it can be ordered.

 

[xenou]

Rank: (SELECT Count(*) 
FROM ZPD_Count1 As U 
WHERE [Rate] > [ZPD_Count1].[Rate] 
AND U.Ord = ZPD_Count1.Ord)[COLOR="Red"][B]+1[/B][/COLOR]

What about taking the +1 off the end? Does that fix the "skipping" problem for the first one?

 

[stapuff]

x -

here is the query:

SELECT ZPD_Count1.Ord, ZPD_Count1.Carrier, ZPD_Count1.Rate, (SELECT Count(*) FROM ZPD_Count1 As U WHERE [Rate] > [ZPD_Count1].[Rate] AND U.Ord = ZPD_Count1.Ord)+1 AS Rank
FROM ZPD_Count1
ORDER BY ZPD_Count1.Ord, ZPD_Count1.Rate;

The result of this is still no correct. Ranks from Highest to lowest, not lowest to highest.

 

[xenou]

SELECT ZPD_Count1.Ord, ZPD_Count1.Carrier, ZPD_Count1.Rate, (SELECT Count(*) FROM ZPD_Count1 As U WHERE [Rate] > [ZPD_Count1].[Rate] AND U.Ord = ZPD_Count1.Ord)+1 AS Rank
FROM ZPD_Count1
ORDER BY ZPD_Count1.[COLOR="Red"]Ord[/COLOR], ZPD_Count1.Rate;

Sorry, but didn't you want this ordered by Rank?
Like this:

SELECT ZPD_Count1.Ord, ZPD_Count1.Carrier, ZPD_Count1.Rate, (SELECT Count(*) FROM ZPD_Count1 As U WHERE [Rate] > [ZPD_Count1].[Rate] AND U.Ord = ZPD_Count1.Ord)+1 AS Rank
FROM ZPD_Count1
ORDER BY [COLOR="Red"]Rank[/COLOR], ZPD_Count1.Rate;

 

[stapuff]

by using the sql statement below and changing the > to < then all is good.