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