Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
How to get max() record?
Message
From
17/01/2004 05:29:11
 
 
To
16/01/2004 22:01:14
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Miscellaneous
Thread ID:
00867397
Message ID:
00867766
Views:
22
John,

you need a criteria for get a row for equal SalesDate ,MaxAmount .
On next example i use MAX(SalesNo), but others are possible;
also RECNO() is possible, but it is probably an ugly one chosen
if reported to database tables.
CREATE CURSOR Sales ( SalesNo c(6), SalesDate D,Amount Y)
INSERT INTO sales VALUES ('A00001',{^2003/01/01},$100)
INSERT INTO sales VALUES ('A00002',{^2003/01/01},$200)
INSERT INTO sales VALUES ('A00003',{^2003/01/02},$100)
INSERT INTO sales VALUES ('A00004',{^2003/01/01},$200)

SELECT MAX(SalesNo), SalesDate, Amount AS MaxAmount ;
FROM Sales S WHERE Amount >= ALL (SELECT Amount FROM Sales WHERE SalesDate=S.SalesDate) ;
GROUP BY 2,3;
INTO CURSOR MaxForDay1

SELECT MAX(SalesNo), SalesDate, Amount AS MaxAmount ;
FROM Sales S WHERE Amount=(SELECT MAX(Amount) FROM Sales X WHERE X.SalesDate=S.SalesDate) ;
GROUP BY 2,3;
INTO CURSOR MaxForDay2
Fabio
Previous
Reply
Map
View

Click here to load this message in the networking platform