asked on

SQL query max

Hello,

The following returns two records:

SELECT V6Quotes.NETT_SELL_PRICE, V6Quotes.QUOTE_VERS
FROM V6Quotes
WHERE (((V6Quotes.QUOTE_NUM)=429693))
GROUP BY V6Quotes.NETT_SELL_PRICE, V6Quotes.QUOTE_VERS;

I just want it to return one. The field QUOTE_VERS is 6 in one record, and 7 in the other record. I need to return the field NETT_SELL_PRICE that has the maximum number in QUOTE_VERS.

Seems simple, but I just can't get it to work. I'm running this query in Access.

Thanks, Joel

ASKER CERTIFIED SOLUTION

grendel777

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

If you want to get all records with max "QUOTE_VERS"

Use this query

SELECT A.QUOTE_NUM,A.NETT_SELL_PRICE, A.QUOTE_VERS
FROM V6Quotes As A
INNER Join (Select Max(B.QUOTE_VERS) As MaxVersion, B.QUOTE_NUM
FROM V6Quotes As B
GROUP By B.QUOTE_NUM) As C
ON A.QUOTE_NUM=C.QUOTE_NUM And A.QUOTE_VERS= C.MaxVersion

Peter Chan

Try

SELECT max(V6Quotes.NETT_SELL_PRICE) as NETT_SELL_PRICE, V6Quotes.QUOTE_VERS
FROM V6Quotes
WHERE (((V6Quotes.QUOTE_NUM)=429693))
GROUP BY V6Quotes.QUOTE_VERS;

Open in new window

PortletPaul

what happens if more that one NETT_SELL_PRICE has the maximum number of quotes for a price?

e.g. $100 quoted 15 times; $120 quoted 15 times

do you still want just one record? if yes, which one?

Genius123

ASKER

This worked perfectly. Thanks!