Use DISTINCT to Eliminate Duplicate Records

The keyword DISTINCT can be used at the beginning of the SELECT statement in a query to eliminate duplicate rows in the result set. That is, based on the fields selected in the SELECT clause, only rows with distinct (different) values in the result set are returned. Rows that have identical values in all selected fields are displayed only once.

This example returns a list of all region values in the custmast file.

SELECT DISTINCT REGON FROM SEQUELEX/CUSTMAST

Five distinct values are returned—10, 20, 30, 40, and 50. Without specifying the DISTINCT keyword, duplicates are not eliminated, and 74 rows are returned—one value for each customer (row) in the file.

This example returns a list of all region values and the associated state values in the custmast file.

SELECT DISTINCT REGON, CSTTE FROM SEQUELEX/CUSTMAST

23 distinct rows are returned—one row for each region/state pair retrieved from the source file. Without specifying the DISTINCT keyword, 74 rows are returned—one row for each customer in the file.

This example returns a list of all region values, the associated state values, and the associated warehouse values for all orders.

SELECT REGON.CUSTMAST, CSTTE.CUSTMAST, HOUSE.ORDHEAD
FROM SEQUELEX/CUSTMAST,SEQUELEX/ORDHEAD
INNER JOIN CUSNO.CUSTMAST=CUSNO.ORDHEAD

25 distinct rows are returned—one row for each region/state/warehouse combination retrieved from the source files. Without specifying the DISTINCT keyword, 76 rows are returned—one row for each customer order in the files.

How to add DISTINCT to a View

To add DISTINCT to your view, do one of the following in the ViewPoint Designer:

  • On the SQL tab, type DISTINCT following the SELECT keyword.
  • On the Files and Fields tab, select the Select Distinct Records Only option.

 

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017