>Is it possible to get the maximum value from a field and other fields corresponding to the same record?
>
>i.e: Given a table with OrderNumber, CustId and total_Price, How do I get the highest total_price for every CustId and the Corresponding OrderNumber in a single query?
>
>I´ve tried something like:
>Select CustId, OrderNumber,max(total_price) from [Tablename] group by CustId
>
>It returned the maximum value of "total_price" for every CustId, but the "OrderNumber" returned is not correct, given that in the source table the total_price for that Order_Number is not the same.
>
>Tks
You might need two queries - one to order the records and another to give you your MAX.
SELECT CustId, OrderNumber, total_price FROM yourtable INTO CURSOR curs1 ORDER BY CustId, total_price
SELECT CustId, LAST(OrderNumber), LAST(total_price) FROM curs1 INTO CURSOR endresult GROUP BY CustId
In the second query, LAST(total_price) and MAX(total_price) should give you the same thing.
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement