>Hi All,
>
>In case of using GROUP BY in SQL-SELECT, The Last record of criteria
>is selected for each group though there is no aggregate field.
>But I heard it's unreliable. Here is my code.
>
>** because records are not inserted in the order of date,
>** select all records from table sorted by desired order
>** before real SQL.
>** Though mytable has INDEX for custid+dtos(date) and I open
>** with proper order with another alias, SQL only tells phisically
>** saved last record.
>
>lcAlias=SYS(2015)
>select custid,date,debt,custid+dtos(date);
> FROM myTable ;
> where date < someday;
> into cursor (lcAlias) order by 4
>
>** get the last debt of each custid
>select custid,MAX(date),debt from (lcAlias);
> GROUP BY CUSTID into cursor myCursor
>** select custid,date,debt from (lcAlias);
>** GROUP BY CUSTID into cursor myCursor
>
>Though I can have the last debt and date per custid correctly,
>I'm not so sure My way is correct.
>And in ther 2nd-Query, Regardless of using MAX() I have the
>same result. I need an advice what is the most reliable way
>to get the last record of some specific order.
>
>Any comments would be appreciated.
>
>RGDS
>HK.Lee
Lee,
Group By gets the last record in physical order. You order your records first then do a 'group by'. It's the right way to do it and would work.
max() wouldn't effect 2nd query because by ordering you cause the last record of a group be same as max() of that group.
There is one thing though. You unintentionally create a 'true cursor' in 1st SQL (by defining a nonexistant field in result set - custid+dtos(date)). Instead make sure you a create a true cursor by adding 'nofilter' clause to SQL.
PS: If there is more than one date entry for the same last date you get the last entered.
Cetin