Obtain Unique Record with For Each Statement

[crjunk]

Can someone show or refer me to an example that will accomplish this? I'll try my best to explain what I would like to do.

I want to obtain the last Customer record in my table using a FOR EACH where the city is unique, ordered by Cust, City.

For instance, if this is the data in my Customer table:

cust city amt
1800 001 222
8000 001 444
1800 001 111
1800 004 555
4000 001 777
1800 002 444
1800 002 777
1800 001 888

I want my FOR STATEMENT to return the following:
cust city amt
1800 001 888
1800 002 777
1800 004 555

I hope this makes sense.

Thanks,
CR Junk

 

[schaapie]

check out the break by and last-of statements

 

[crjunk]

I understand the Break By and Last Of statements. I guess my hangup is how to get each unique city code.

Thanks,
CR Junk

 

[schaapie]

pasted your cust-table example in c:\temp\1.csv
and run following code:

def temp-table tcust
    field cust as int
    field city as int
    field amt  as int
    index cust-city cust city.
input from c:\temp\1.csv.
repeat :
    create tcust.
    import tcust.
end.
delete tcust.
for each tcust break by city by amt.
  if last-of(city) then
    disp tcust.
end.

It doesnt matter what index is on the temp-table, the break by re-sorts

 

[vinod_home]

You dont have to use a temp-table if you dont want to..

Change your For each

For each customer no-lock
where <where clause>
break by city by cust by amt:

/* depending on which customer you want */
if last-of (city) then
or
if first-of (city) then

display customer.

 

[joey.jeremiah]

/* in sql */

select ordernum,salesrep,custnum
from order
group by custnum.


/* in 4gl, theres no group by */

/* if theres an index on the grouping value */
/* the most effecient/optimized way */
/* would be to skip thru the list using find first/next */

/* otherwise you can use */
/* for each, break by and first-of */

find first order
use-index custorder
no-lock no-error.

repeat while avail order:

display ordernum salesrep custnum.

define var lastCustNum as int no-undo.
lastCustNum = custnum.

find next order
where custnum > lastCustNum
use-index custorder
no-lock no-error.

end. /* repeat */

/* i can't seem to find the article in the knowledge base */
/* i also remember seeing it on peg */

 

[gcampbell]

/* in 4gl, theres no group by */


What do you mean there is no Group By in the 4GL? See the previous posts on 'Break By'.

 

[John Nebi]

Joey.jeremiah may may be thinking of just plain "by" rather than "break by". That is I think, the equivalent of the SQL "group by".

 

[joey.jeremiah]

break by in 4gl like order by in sql
mainly sorts a list

group by on the other hand
summarizes a list for specfied columns

going back to the original example
instead of having a detailed list of all customers
the group by clause will summarize\group the list into cities

try the following examples from the editor with the sports2000 db
progress supports embedded sql89 standard
although it's being depricated

select ordernum,custnum from order ORDER BY custnum.

select ordernum,custnum from order GROUP BY custnum.

 

[John Nebi]

I stand corrected. I am extremely dangerous with SQL.