• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Select Distinct <> First Of

Been trying to figure this one out for a a while w.o. success. What am I missing here?

OE 11.X

They say SELECT DISTINCT = BREAK BY & FIRST OF: Progress KB - What is an ABL equivalent for a SQL SELECT DISTINCT?

FOR EACH icsec NO-LOCK WHERE cono = 1 AND icsec.altprod = '33-835'
   BREAK BY icsec.altprod BY icsec.prod BY icsec.custno:
        IF FIRST-OF(icsec.altprod) AND FIRST-OF(icsec.prod) AND
FIRST-OF(icsec.custno) THEN
DISP icsec.altprod icsec.prod icsec.custno.
33-835 076174338355 0

SELECT DISTINCT icsec.altprod, icsec.prod, icsec.custno
FROM icsec
WHERE icsec.alt-prod = '33-835'
33-835 076174338355 0
33-835 10092080 1608
33-835 1857625 2716
33-835 1857625 9300
33-835 1857625 9310
33-835 1857625 9313
33-835 1857625 9314
33-835 1857625 9316
33-835 1857625 9317
Last edited by a moderator:


ProgressTalk.com Sponsor
Why would you expect these to be the same. In the first on you used two fields to make the selection; in the second you used one. In the first you are ganging together three FIRST-OF, which is obviously not doing what you think. On the very first record, all three fields will be first-of, but when the next unique value of the second field shows up, the first field will no longer be first-of, so that won't print. Likewise for both the first and second fields when there is a break by the third field.

And, of course, there is always the chance of a different index being used.


ProgressTalk.com Moderator
Staff member
What happens if you change it to:

IF FIRST-OF(icsec.altprod) OR FIRST-OF(icsec.prod) OR
FIRST-OF(icsec.custno) THEN
Ooops - Yea, typoed the sql query example above. Adding the same criteria, cono = 1 does yield the same result as depicted though, which was what Intended to show.

I was just trying following the example in the Progress KB:
Works in OpenEdge 10.x and 11.x

Replace "SomeTable, "SomeConditions", "SomeField1" and "SomeField2" with your values

FOR EACH SomeTable NO-LOCK WHERE SomeConditions BREAK BY SomeField1 BY SomeField2:
/* Do What You Want With The Record Here */

Thanks - OR produced the desired result!