Select Distinct <> First Of

pinne65

Member
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?

BUT
Code:
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.
END.
=>
33-835 076174338355 0
AND

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:

tamhas

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.
 

Cringer

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
 

pinne65

Member
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:
IF FIRST-OF(SomeField1) AND FIRST-OF(SomeField2) THEN
/* Do What You Want With The Record Here */
END.

Thanks - OR produced the desired result!
 
Top