sql distinct max duplicating rows

I am trying to get a select statement to give me the max ordernum for distinct item number along with two other fields.  when running just the distinct and max I get the correct number of rows but when adding the other fields by it duplicates many rows.  I have tried this a lot of ways and nothing is returning what I would expect.   I am hoping someone can see my mistake.

SELECT DISTINCT ordr.ItemCode, MAX(ordr.ordrnum) AS ordrnum, ordr_1.BaseCard, ordr_1.Price
FROM         POR1 LEFT OUTER JOIN
                      ordr AS ordr_1 ON ordr.DocEntry = ordr_1.DocEntry
GROUP BY POR1.ItemCode, POR1_1.Price, POR1_1.BaseCard
notasgoodasyouAsked:
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.

dsackerContract ERP Admin/ConsultantCommented:
You'll need to join by the MAX(ordr.ordrnum), as follows:

SELECT  ordr.ItemCode,
        ordr.ordrnum,
        ordr.BaseCard,
        ordr.Price
FROM    ordr
JOIN   (SELECT ItemCode, MAX(ordrnum) AS maxOrdr
        FROM   ordr
        GROUP BY ItemCode) o2
ON      o2.ItemCode = ordr.ItemCode
AND     o2.ordrnum  = ordr.ordrnum

Open in new window

I changed the SQL up a little bit, as there was no relationship to your table POR1 and the ordr table. If you need to join POR1 (and it is indeed a valid table), simply add that join at the bottom.
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
Scott PletcherSenior DBACommented:
Can you re-do your initial SQL so that it's valid?  I can't tell for sure which columns come from which tables.
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 2008

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.