Selecting Specific Rows from a Select Query

[StayAtHomeMom]

I have a Select Query from a table that chooses a part number, a customer name, and a sum of the Dollar Amount. It is in order of part number, and then descending dollar amount for each of the customers of that part number. See SQL below:

SELECT [POS Detail].PtNo, [POS Detail].CustName, Sum([POS Detail].CstExt) AS SumOfCstExt
FROM [POS Detail]
GROUP BY [POS Detail].PtNo, [POS Detail].CustName
ORDER BY [POS Detail].PtNo, Sum([POS Detail].CstExt) DESC;

What I’d like to extract from the results is just the FIRST record of every part number, which in essence, gives me the customer name and sales with the largest dollar amount for that part number. I don’t care about the rest.

Is there a property I can set to do that in a subsequent query on those results? Am I going about this in the wrong way?

Thanks in advance for your expertise.

 

[lars7]

Maybe something like this:

SELECT A.PartField, A.NameField, Max(A.MoneyField)
FROM A
GROUP BY A.PartField, A.NameField;

 

[lespaul]

I first have to ask, why are you storing the customer name in the [POS Details] table.....secondly, it sounds like you want a list of partnumbers (a single record for each partnumber) and for each partnumber you want to know who purchased the highest dollar amount of that partnumber and the name of that customer, is that right?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins