GROUP BY Problem

[AllanB1]

The following SELECT statement doesn't necessarily return the desired record, indicating the QTY purchased on the date MAX(DATEBOT). I realize this result is correct, based on my statement.

SELE PN,QTY,MAX(DATEBOT) LATEST;
FROM INVOICES;
GROUP BY PN

However, I do need to return the exact record that corresponds to MAX(DATEBOT). Can this be done through a SELECT statement? I know how to do it using SCAN..ENDSCAN, but SELECT would be better if it can be done.

Appreciate any advice.

Thanks.

 

[Stella740pl]

Would two SELECTs work for you?

Say, like this:

SELECT Pn, MAX(DateBot) AS Latest ;
   FROM Invoices ;
   GROUP BY Pn ;
   INTO CURSOR Tmp

SELECT Pn, Qty, DateBot ; 
   FROM Invoices ;
   WHERE STR(Pn,4)+DTOS(DateBot) IN ;
   (SELECT STR(Pn,4)+DTOS(Latest) FROM Tmp) ;
   INTO CURSOR TheLatest

(That is, if Pn is numeric. If it is character, there is no need to convert it.)

It's possible that someone will think of a single SELECT solution, but this is what comes to mind now.

 

[JimWWinter]

SELECT Pn, Qty, MAX(DateBot) AS Latest ;
FROM Invoices ;
GROUP BY Pn, Qty ;
INTO CURSOR Tmp


Regards,
Jim

 

[Stella740pl]

Jim,

This will find a separate MAX(DateBot) for every existing Qty of each Pn in the table, not only for every Pn (which appears to be some kind of a key). It has a potential of finding nearly every record in the table, not only the latest for each Pn.

 

[AllanB1]

Stella:
I hadn't considered a nested SELECT, which I think will work. I'm not at my development system now, so I can't try it, but it looks like it will work fine. I will let you know.

Jim:
I had tried what you suggested already, but as Stella said, this groups by PN (part number - Char), then QTY (quantity).

Thank you both.

Allan

 

[JimWWinter]

Yep, guess I posted without thinking it through. ;-(

Regards,
Jim