How to develop this query?

[Former Member]

Hi all,

I have a requirement similar to this- I have a list of planned costs for different phases in a single calendar year. So this list looks like this:

CALYEAR PHASE PCOSTS

2004 P1 1,200

2004 P2 900

2004 P3 1,300

2005 P1 1,400

2005 P2 1,200

2005 P3 800

2005 P4 700

Now I have to define a query which plots these costs against the position of each cost in the value range for a year. Simply put, I'll have to find out the olympic rank of each cost data within all the cost data for each year. So, 2004 will have a range of 3 values and 2005 will have a range of 4 values.

After this I'll have to find out the max. rank for each year.

So can someone please tell me how to go about this?

Thanks,

Satyajit.

[Former Member]

Hi all,

to further exemplify my point, the entire list will look something like this

CALYEAR PHASE COSTS RANK IN RESPECTIVE YEAR

2004 P1 1,200 2

2004 P2 900 1

2004 P3 1,300 3

2005 P1 1,400 4

2005 P2 1,200 3

2005 P3 800 2

2005 P4 700 1

This Rank column will have to be calculated by me. In case of the above list, Maximum Rank in 2004 is 3 and Maximum Rank in 2005 is 4.

I'll need to calculate the Max. Ranks also.

So how do I define my query to do this? Please help.

Thanks,

Satyajit.

[Former Member]

Any help anyone?