Select Max query - please help

[Brad Parks]

The following are a few rows in my table:

StatedValue VehType Deductible Rate
10000 Truck 1000 9.16
11000 Truck 1000 9.02
12000 Truck 1000 8.82
13000 Truck 1000 8.64
14000 Truck 1000 8.51
15000 Truck 1000 8.33

SELECT Rate FROM table where StatedValue <= 13775 AND VehType='Truck'
AND Deductible=1000 (obviously, returns 4 rows).

But, I only want the Rate(8.64) from the row containing the highest
StatedValue returned from the above query.

Please help modify this query.

[Zach Wells]

Re: Select Max query - please help

Just add MAX() to it:

SELECT MAX(rate)
FROM table
WHERE StatedValue <= 13775
AND VehType = 'Truck'
AND Deductible = 1000

Zach

Brad Parks wrote:[color=blue]
> The following are a few rows in my table:
>
> StatedValue VehType Deductible Rate
> 10000 Truck 1000 9.16
> 11000 Truck 1000 9.02
> 12000 Truck 1000 8.82
> 13000 Truck 1000 8.64
> 14000 Truck 1000 8.51
> 15000 Truck 1000 8.33
>
> SELECT Rate FROM table where StatedValue <= 13775 AND VehType='Truck'
> AND Deductible=1000 (obviously, returns 4 rows).
>
> But, I only want the Rate(8.64) from the row containing the highest
> StatedValue returned from the above query.
>
> Please help modify this query.[/color]

[Point maker]

Re: Select Max query - please help

select Rate from table where StatedValue =
(select max(StatedValue ) from table)
"Brad Parks" <bradfordparks@ yahoo.com> wrote in message
news:bf029bae.0 405181730.5f72b 79@posting.goog le.com...[color=blue]
> The following are a few rows in my table:
>
> StatedValue VehType Deductible Rate
> 10000 Truck 1000 9.16
> 11000 Truck 1000 9.02
> 12000 Truck 1000 8.82
> 13000 Truck 1000 8.64
> 14000 Truck 1000 8.51
> 15000 Truck 1000 8.33
>
> SELECT Rate FROM table where StatedValue <= 13775 AND VehType='Truck'
> AND Deductible=1000 (obviously, returns 4 rows).
>
> But, I only want the Rate(8.64) from the row containing the highest
> StatedValue returned from the above query.
>
> Please help modify this query.[/color]

[Viktor Pryganov]

Re: Select Max query - please help

Hi, Point!
You wrote on Wed, 19 May 2004 04:10:55 GMT:

select Rate from table
where StatedValue=(se lect max(StatedValue ) from table)
AND StatedValue<=13 775 AND VehType='Truck' AND Deductible=1000

-
exexe

[Viktor Pryganov]

Re: Select Max query - please help

Hi, Brad!

select Rate from table
where StatedValue=(se lect max(StatedValue ) from table
where StatedValue<=13 775
AND VehType='Truck' AND
Deductible=1000 )

-

[Brad Parks]

Re: Select Max query - please help

Thanks Viktor! It worked.

I had to modify it to the following since there were multiple
instances of the same StatedValue for different Deductibles and
VehTypes:

select Rate from table where
StatedValue=(se lect max(StatedValue ) from table
where StatedValue<=13 775 AND VehType='Truck'
AND Deductible=1000 ) AND VehType='Truck' AND Deductible=1000