I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

DISTINCT applies to ALL columns you specify in SELECT.  If you want a distinct SSN, then you need to specify only SSN column in SELECT but this is not what you want.   For what you want, in DB2 for i, there is this FETCH FIRST n ROWS ONLY for use.  If you want only the very first row of the result set, you use FETCH FIRST ROW ONLY at the end of SELECT.

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

fetch first row only gives you just 1 row in the file.  I want a result set that includes the first row only of each multiple occurrence of an SSN...

DISTINCT applies to ALL columns you specify in SELECT.  If you want a distinct SSN, then you need to specify only SSN column in SELECT but this is not what you want.   For what you want, in DB2 for i, there is this FETCH FIRST n ROWS ONLY for use.  If you want only the very first row of the result set, you use FETCH FIRST ROW ONLY at the end of SELECT.

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

This article may also give you more choices to consider : How to Join Only the First Row in SQL at https://learnsql.com/blog/sql-join-only-first-row/

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

This article may also give you more choices to consider : How to Join Only the First Row in SQL at https://learnsql.com/blog/sql-join-only-first-row/

I have specified distinct on SSN, so I guess it is not working wrong, just not how I specified...  is there a way to get only the first unique SSN occurrence, and use the columns from that occurrence and drop the other occurrences, like a chain and update when not found in RPG?

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

As Birgitta said, Distinct is NOT a function nor can it be used on a subset of columns being selected.
Once this is known, queries written with immediate parenthesis will no longer be misleading.

What if you were writing a query like this?
'select distinct (select 1 from table1 where ...), field2, field3 from table1 where...'

Sure, this may look misleading, but if we didn't allow parenthesis, then contained logic or sub-selects wouldn't be allowed when using distinct.

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

Distinct, when used in column selection, is confusing. I've instructed my shop to not use it and specify the Group By clause. I haven't thought much about the value of 'Is Distinct From' (Distinct predicate). My advice to David is to do the same and use Group By.

As Birgitta said, Distinct is NOT a function nor can it be used on a subset of columns being selected.
Once this is known, queries written with immediate parenthesis will no longer be misleading.

What if you were writing a query like this?
'select distinct (select 1 from table1 where ...), field2, field3 from table1 where...'

Sure, this may look misleading, but if we didn't allow parenthesis, then contained logic or sub-selects wouldn't be allowed when using distinct.

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

Distinct, when used in column selection, is confusing. I've instructed my shop to not use it and specify the Group By clause. I haven't thought much about the value of 'Is Distinct From' (Distinct predicate). My advice to David is to do the same and use Group By.

------------------------------
Patrick Conner

Original Message:
Sent: Wed August 23, 2023 12:13 PM
From: Mike Zaringhalam
Subject: distinct not returning unique results

As Birgitta said, Distinct is NOT a function nor can it be used on a subset of columns being selected.
Once this is known, queries written with immediate parenthesis will no longer be misleading.

What if you were writing a query like this?
'select distinct (select 1 from table1 where ...), field2, field3 from table1 where...'

Sure, this may look misleading, but if we didn't allow parenthesis, then contained logic or sub-selects wouldn't be allowed when using distinct.

------------------------------
Mike Zaringhalam

Original Message:
Sent: Wed August 23, 2023 08:10 AM
From: Patrick Conner
Subject: distinct not returning unique results

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

------------------------------
Patrick Conner

Original Message:
Sent: Tue August 22, 2023 10:13 AM
From: Birgitta Hauser
Subject: distinct not returning unique results

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Tue August 22, 2023 08:14 AM
From: Patrick Conner
Subject: distinct not returning unique results

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

------------------------------
Patrick Conner

Original Message:
Sent: Sat August 19, 2023 05:11 AM
From: Birgitta Hauser
Subject: distinct not returning unique results

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Fri August 18, 2023 04:22 PM
From: David Strawn
Subject: distinct not returning unique results

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

------------------------------
David Strawn
------------------------------

I may be thinking too much like an RPG programmer, but I think it would be helpful to have a conditional group by/distinct, where you can do something like ...group by ssno use first occurence..., this would enable you to use the non-grouped columns.  i know that implies a non normalized database, but in my world that is common.  I know there are other ways to get around this, but for a one off sql select script, it would be nice...   imo

------------------------------
David Strawn

Original Message:
Sent: Thu August 24, 2023 09:19 AM
From: Patrick Conner
Subject: distinct not returning unique results

Distinct, when used in column selection, is confusing. I've instructed my shop to not use it and specify the Group By clause. I haven't thought much about the value of 'Is Distinct From' (Distinct predicate). My advice to David is to do the same and use Group By.

------------------------------
Patrick Conner

Original Message:
Sent: Wed August 23, 2023 12:13 PM
From: Mike Zaringhalam
Subject: distinct not returning unique results

As Birgitta said, Distinct is NOT a function nor can it be used on a subset of columns being selected.
Once this is known, queries written with immediate parenthesis will no longer be misleading.

What if you were writing a query like this?
'select distinct (select 1 from table1 where ...), field2, field3 from table1 where...'

Sure, this may look misleading, but if we didn't allow parenthesis, then contained logic or sub-selects wouldn't be allowed when using distinct.

------------------------------
Mike Zaringhalam

Original Message:
Sent: Wed August 23, 2023 08:10 AM
From: Patrick Conner
Subject: distinct not returning unique results

Birgitta,

The result row...
Based on your example you could write a query such as:

select distinct( col1 ), col2, col3 ... colN
from Table

"Distinct" doesn't apply to the uniqueness of col1, but all selected columns. Why allow the statement to be written this way? It is misleading.

------------------------------
Patrick Conner

Original Message:
Sent: Tue August 22, 2023 10:13 AM
From: Birgitta Hauser
Subject: distinct not returning unique results

It does not apply to the whole row only the selected columns.

It is the same as:

SELECT Col1, Col2, Col3 ... ColNFrom TableGroup By Col1, Col2, Col3, ... ColN

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Tue August 22, 2023 08:14 AM
From: Patrick Conner
Subject: distinct not returning unique results

The follow-up question is "why does distinct allow a column name if it applies to the whole row only?"

------------------------------
Patrick Conner

Original Message:
Sent: Sat August 19, 2023 05:11 AM
From: Birgitta Hauser
Subject: distinct not returning unique results

DISTINCT removes duplicates if all column values of two or more rows are identical.

If I look at the result. In the second column you have different values in the first and second row.

So DISTINCT works correct.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Fri August 18, 2023 04:22 PM
From: David Strawn
Subject: distinct not returning unique results

I have the following SQL script with a distinct on pm.ssno:

select
   distinct(substr(right(digits(pm.ssno), 9), 1, 3) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 4, 2) concat '-' 
      concat substr(right(digits(pm.ssno), 9), 6, 4)) as "SSN",
   digits(pm.empnum) as "Employee Number",
   nm.fn as "First Name",
   nm.in as "Middle Initial",
   nm.ln as "Last Name",
   nm.sx as "Suffix",
   pm.addr as "Home Address Line 1",

.

.

from gpl.paymasvw pm 
join gpl.prsnamevw nm on nm.ssno = pm.ssno
left join dlsf.emsemps em on ucase(em.FrstName) = nm.fn and ucase(em.LastName) = nm.ln
where dot >= (bigint(current_date) - 5000) or dot = 0
order by SSN
;    

Here is the result set - can't show ssn. you will just have to trust me that they are the same...

I may be missing something about the distinct statement, but not sure what

------------------------------
David Strawn
------------------------------