complicated query

I have a table which has 4 columns
Sales_person, vehicle_id, price, date
I want a query to return names of the sales persons who sold the
vehicle at a record price in the month of may 2006.
say if sales person A sold a car for 25k in April 06 and 26k in may 06
I want the record high price for all sales if occured in may 06. But if
B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
2005 I do not want that record.
any help with this query.I'm just guessing at what you want, but here are two
possibilities.
Since there is only one record high price, maybe you
want just want all sales at the record high price that
happened to occur in May, 2005:
select
Sales_person, Vehicle_id, price, date
from T
where
date >= '20050501'
and date < '20050601'
and price = (
select max(price)
from T
)
Your explanation is more complicated, but I can't figure
out what else it might mean.
Steve Kass
Drew University
http://www.stevekass.com
VJ wrote:

>I have a table which has 4 columns
>Sales_person, vehicle_id, price, date
>I want a query to return names of the sales persons who sold the
>vehicle at a record price in the month of may 2006.
>say if sales person A sold a car for 25k in April 06 and 26k in may 06
>I want the record high price for all sales if occured in may 06. But if
>B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
>2005 I do not want that record.
>any help with this query.
>
>|||Look into datepart and max
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1149533614.614289.111110@.h76g2000cwa.googlegroups.com...
>I have a table which has 4 columns
> Sales_person, vehicle_id, price, date
> I want a query to return names of the sales persons who sold the
> vehicle at a record price in the month of may 2006.
> say if sales person A sold a car for 25k in April 06 and 26k in may 06
> I want the record high price for all sales if occured in may 06. But if
> B sold 2 cars for 26k, 28k in may 06 but another car for 30k in dec
> 2005 I do not want that record.
> any help with this query.
>