Search code examples
sqldatabaseibm-midrange

AS400 SQL - Select records from a file where there are unique values based on the grouping of another column

Calling all AS400 SQL gurus- Imagine you have a database as such

Client DATA 1
ABC 123
ABC 123
ABC 123
EFG 456
LMN 678
QRS 555
QRS 556
XYZ 789
XYZ 789
XYZ 999

I'm seeking an SQL solution that will group the Client field and inspect DATA 1 and identify all instances where any DATA 1 differs within a specific client. So for example - -Client ABC - all of DATA 1 for client ABC are identical so do NOT select. -Client EFG - A single so DATA 1 will ALWAYS match so do NOT select. -Client LMN - A single so DATA 1 will ALWAYS match so do NOT select. -Client QRS- 2 records and the DATA 1 records do not match -Include all in the SQL selection -Client XYZ - 2 records are identical but a single record does not match - Include all in the SQL selection.

So in the end my SQL selection should look like -

Client DATA 1
QRS 555
QRS 556
XYZ 789
XYZ 789
XYZ 999

That is to say- I have a listing of all records where DATA 1 is NOT identical within a Client grouping.

FWIW - I've tested UNIQUE, GROUP BY, HAVING COUNT, etc.....and I still find myself stuck. Thanks in advance for any and all of your help.

FWIW - I've tested UNIQUE, GROUP BY, HAVING COUNT, etc.....and I still find myself stuck.

Solution

  • You could use a CTE or sub-query to get the clients which have more than one distinct data_1 value.

    select *
  from table1
where client in (
  select client
    from table1
  group by client
  having count(distinct data_1) > 1
  ) --alias if needed