CODEWITHSUNDEEP
visual-foxpro
DClark
DClark

Reputation: 3

How to Include Table Element Without Changing Grouping

How do I add a table element without changing grouping? This select works as intended:

SELECT cid, cnumber, cenrtyp, MAX(nqpoints);
from demographicenroll3.dbf;
WHERE cenrtyp = "RT" ;
GROUP BY cid, cnumber, cenrtyp ;

This gives me a list which includes only one of the records in the group cid, subgroup cnumber, that has the max nqpoints value, which is what I want.

But I need to include the unique record number of cnumber in my recordset, which is ecid - so I add ecid:

SELECT cid, cnumber, cenrtyp, MAX(nqpoints), ecid;
from demographicenroll3.dbf;
WHERE cenrtyp = "RT" ;
GROUP BY cid, cnumber, cenrtyp, ecid ;

But this doesn't work because it changes the grouping making the Max(nqpoints) incorrect. Any tricks to get what I want?

Upvotes: 0

Views: 36

Answers (1)

Tamar E. Granor
Tamar E. Granor

Reputation: 3937

You can't do that in a single query, but you can by making your existing query into a derived table in another query:

SELECT dmax.cid, dmax.cnumber, dmax.cenrtyp, maxpts, ecid ;
FROM demographicenroll3 d3
  JOIN (
     SELECT cid, cnumber, cenrtyp, MAX(nqpoints) AS maxpts;
     from demographicenroll3;
     WHERE cenrtyp = "RT" ;
     GROUP BY cid, cnumber, cenrtyp) dmax ;
    ON d3.cid = dmax.cid ;
    AND d3.cnumber = dmax.cnumber ;
    AND d3.cenrtyp = dmax.cenrtype ;
    AND d3.nqpoints = dmax.maxpts

Upvotes: 1

Related Questions