distinct maximum value

[shaminda]

I have a table as follows

Cust Month Price
A 1 98.3
A 2 93.2
A 3 96.9
A 4 92.1
B 1 12.4
B 2 45.6
B 3 97.8
C 1 78.3
C 2 78.5
C 3 97.2
C 4 98.8
C 5 56.7

I want to return the only the records that has the maximum month for each cust. The result will be as follows

A 4 92.1
B 3 97.8
C 5 56.7

How do I write a querry in SQL for an Access database?

 

[grahamnewlands]

There are many ways but here is one :

select cust, max(mnth)
from gtntest
group by cust

The above will give you the customer and the month. You can open record set for each row returned and do another select to get the price based on the customer and month in each record i.e.

select price from gtntest
where cust = :cust /* :cust is a variable */
and month = :month /* :month is a variable */

Hope this helps

 

[grega]

This query should do it (works in Sybase anyway!).

select cust,month,price
from table a
where month=(select max(month) from table b
where b.cust=a.cust)

Greg.

 

[shaminda]

Greg,

the program is promting for month, so if I enter say 5 it will show only one record

c 5 56.7

but I want the results for all months.