Select Distinct?

[Zugdud]

Howdy folks, Im using Crystal Reports 8.5 using an ODBC connection to a Progress 9.1D Database. I seem to be having a problem using DISTINCT, mabey im doing it wrong. I tried adding DISTINCT infront of the field in the select portion of the SQL, like DISTINCT address1."Name" but it doesn't seem to work. I want to have select only distinct values from 3 fields, address1, address2, address3.

Here is my SQL statement:

SELECT
Customer1."Name",
ShipTo1."Address1", ShipTo1."Address2", ShipTo1."City", ShipTo1."State", ShipTo1."ZIP", ShipTo1."ShipToNum", ShipTo1."Company", ShipTo1."Address3",
CustCnt1."Name", CustCnt1."FaxNum", CustCnt1."PhoneNum"
FROM
"PUB"."Customer" Customer1,
"PUB"."ShipTo" ShipTo1,
"PUB"."CustCnt" CustCnt1
WHERE
Customer1."CustNum" = ShipTo1."CustNum" AND
Customer1."Company" = ShipTo1."Company" AND
ShipTo1."Company" = CustCnt1."Company" AND
ShipTo1."CustNum" = CustCnt1."CustNum" AND
ShipTo1."ShipToNum" = CustCnt1."ShipToNum" AND
Customer1."Name" <> 'Bortek Industries' AND
ShipTo1."Company" = '101' AND
ShipTo1."ShipToNum" <> '999'
ORDER BY
ShipTo1."ShipToNum" ASC,
Customer1."Name" ASC

Thanks for any help!

 

[fredp1]

Select distinct can be found under File, report options... but thats not what you need to solve your problem.
Distinct works for the whole record, not just specific fields on your report/ Sleect distinct is performed by the db.

Your trying to group on a Customer name, then summarise the some details, max(Address1) or the last value of address1 etc.

SO create a group on Customer name
Copy/move the detail fields into the Group Footer for Customer name then suppress the details. This will give you the last value for each of the fields in the group.
Alternatively you could summarise those fields, by using the max/min function on those fields your wish to display.

Cheers
Fred