🚀 go-pugleaf

Hi,

After much reading of posts in these newsgroups, I am at a quandary. 
Before launching into a long description, a simple example will
probably do better:

My table with sample records:

CustNo             ProdName           Cost
123                Hammer             7.99
123                Saw                12.99
123                Paint Brush        2.49
123                Sand Paper         1.59
555                Spakeling          4.59
555                Drill Bit Set      15.99

I want an SQL statement to produce ONE record for each customer which
lists the most expensive item he purchased.  Example:

CustNo             ProdName           Cost
123                Saw                12.99
555                Drill Bit Set      15.99


The SQL statement I tried was:

   SELECT CustNo, Max(Cost) AS MaxCost, ProdName FROM ORDERS
   GROUP BY CustNo

I get the resulting error:  

   "When GROUP BY exists, every simple field in projectors must be in
GROUP BY."

If I do end up putting the ProdName field in the GROUP BY clause, the
result set end is NOT what I want.  It looks like:

   SELECT CustNo, Max(Cost) AS MaxCost, ProdName FROM ORDERS
   GROUP BY CustNo, ProdName

CustNo            MaxCost            Prodname
123               7.99               Hammer
123               2.49               Paint Brush
123               1.59               Sand Paper
123               12.99              Saw
555               15.99              Drill Bit Set
555               4.59               Spakeling


Okay, I now realize that all non-aggregate fields must be in the GROUP
BY clause, but it will ALWAYS not produce what I want.

What alternate can I use to achieve the 2 record result set I am
looking for?  I must be overlooking something really simple.

Thanks in advance,
Pulling my Hair Out