Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: tushar Kanvinde
  Where is tushar Kanvinde?
 Kolhapur, Maharastra
 India
 tushar Kanvinde
 Tags
Subject: RE: select max & min for the latest grade
Thread ID: 248987 Message ID: 249010 # Views: 1 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Saturday, December 26, 2009 6:50:13 PM         
   


> Try
>
>
CREATE CURSOR TMP (IDNO C(4), DATE d, GRADE C(2), LEVEL i, AMOUNT n(12,0))
> INSERT INTO tmp VALUES ("0001", {^2001/11/11}, "AM", 1, 200)
> INSERT INTO tmp VALUES ("0001", {^2002/11/12}, "AM", 2, 220)
> INSERT INTO tmp VALUES ("0001", {^2003/11/14}, "AM", 3, 250)
> 
> INSERT INTO tmp VALUES ("0001", {^2005/11/11}, "SM", 1, 400)
> INSERT INTO tmp VALUES ("0001", {^2006/11/12}, "SM", 2, 460)
> INSERT INTO tmp VALUES ("0001", {^2007/11/13}, "SM", 3, 570)
> 
> INSERT INTO tmp VALUES ("0002", {^2001/11/11}, "CM", 1, 1100)
> INSERT INTO tmp VALUES ("0002", {^2002/11/12}, "CM", 2, 1220)
> INSERT INTO tmp VALUES ("0002", {^2003/11/13}, "CM", 3, 1250)
> 
> INSERT INTO tmp VALUES ("0002", {^2005/11/11}, "HE", 1, 1500)
> INSERT INTO tmp VALUES ("0002", {^2006/11/12}, "HE", 2, 1660)
> INSERT INTO tmp VALUES ("0002", {^2007/11/13}, "HE", 3, 1870)
> 
> 
> SELECT MAX(DATE) as DATE,IDNO FROM tmp GROUP BY IDNO INTO CURSOR Temp
> SELECT TEMP.*,tmp.GRADE FROM TEMP LEFT JOIN tmp ON tmp.IDNO=TEMP.IDNO and tmp.DATE=TEMP.DATE INTO CURSOr TEMP1
> 
> SELECT tmp.IDNO,MIN(tmp.DATE),tmp.GRADE, MAX(tmp.DATE), MAX(tmp.AMOUNT),MIN(tmp.AMOUNT) FROM tmp LEFT JOIN temp1 ON temp1.idno=tmp.idno AND temp1.grade=tmp.grade WHERE temp1.grade is NOT null GROUP BY tmp.idno,tmp.grade
> 

>
> Regards
> Tushar

Can we really be sure that MIN(amount) and MAX(amount) correspond to MIN(date) and MAX(date) respectively for any given idno and grade. They do in this sample data, but is there such an internal dependency in the real data?

-Anders

ENTIRE THREAD

select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 9:24:46 AM
RE: select max & min for the latest grade Posted by M. Akram Bhatti @ 12/26/2009 9:32:35 AM
RE: select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 9:44:51 AM
RE: select max & min for the latest grade Posted by M. Akram Bhatti @ 12/26/2009 10:10:18 AM
RE: select max & min for the latest grade Posted by Leonard Trevor @ 12/26/2009 12:15:00 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 12:14:41 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 12:55:56 PM
RE: select max & min for the latest grade Posted by tushar @ 12/26/2009 1:54:52 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 6:50:13 PM
RE: select max & min for the latest grade Posted by Anders Altberg @ 12/26/2009 4:29:10 PM