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
Genius123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

grendel777Commented:
You can use "TOP 1" combined with an ORDER BY clause to get it:

SELECT TOP 1
	v6.NETT_SELL_PRICE, 
	v6.QUOTE_VERS
FROM V6Quotes v6
WHERE v6.QUOTE_NUM = 429693
ORDER BY v6.QUOTE_VERS DESC

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
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
0
HuaMin ChenProblem resolverCommented:
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

0
PortletPaulfreelancerCommented:
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?
0
Genius123Author Commented:
This worked perfectly.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.