>>>I am having trouble isolating records in a table that have the highest price but the nouns are the same. So if there are 50 tables, 25 chairs, 200 lamps with varying prices for each, I want a list of 3 items, a table, a chair, and a lamp, with each of the 3 having the highest price in its category.
>>>Is there a way to prg this in foxpro or is sql needed?
>>
>>
SQL is the easiest way Tim IMHO.
>>Select itemname, max(price) as Highest from mytable group by 1
>
>Cetin, I have implemented the following:
>select *,max(price) from l:dlytrans group by LMCA_NBR;
>where todaydt=CTOD(CHOSDATE) and price>=2500 INTO CURSOR Ernie DIST
>
>now there are 3 items with same lmca_nbr and 3 different prices but the one with the cheapest price shows up. I want the most expensive.
Group by is valid only for aggregation fields + ones that have unique value. Other than that you always get the last physical entered record of group. If you need the whole record than you have to be tricky :
select * from l:dyntrans ;
where todaydt=CTOD(CHOSDATE) and ;
padl(LMCA_NBR,10,'0')+padl(price,10,'0') in ;
(select max( padl(LMCA_NBR,10,'0')+padl(price,10,'0') ) ;
from l:dyntrans group by LMCA_NBR) ;
into cursor myCursor
If you add recno() as a select field (select *, recno() as rcno ...) than you have the actual recnos too.
Cetin