General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
In my SQL statement below, I retrieve not only the numsales field which has the MAX value, but the company and zip from that record. As a result, it lists every company and zip which has the largest numsales for each custnum. This code looks like a kludge and I'm not 100% certain it works. If any of you know of a better way, I'll love to know.
******** CODE ********
SELECT custnum, MAX(numsales) AS numsales, ;
LEN(MAX(numsales)) AS numsales_len, ;
MAX(numsales+company) AS company, ;
MAX(numsales+zip) AS zip ;
FROM db_main ;
GROUP BY custnum ;
INTO CURSOR tmp_cursor0
SELECT custnum, numsales, ;
SUBSTR(company, numsales_len+1) AS company, ;
SUBSTR(zip, numsales_len+1) AS zip ;
FROM tmp_cursor0 ;
INTO CURSOR tmp_cursor
******** END CODE ********
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only