[stackoverflow] [progress Openedge Abl] Selecting Distinct Value From One Column And...

Status
Not open for further replies.
T

TheRealKernel

Guest
I have the following query that returns the rows where there is duplicates on some field,

SELECT customer_id, first_nm, last_nm, middle_nm, member_id, secret_field
FROM cust_tbl
WHERE secret_field <> '' AND
secret_field IN (SELECT secret_field
FROM cust_tbl
WHERE secret_field <> ''
GROUP BY secret_field
HAVING COUNT(secret_field) >= 2

)`


Which returns the desired results, for example,

customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
2 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2


The problem is I want only the rows where 'member_id' isn't duplicated, so the desired return value would be

customer_id | first_nm | last_nm | middle_nm | member_id | secret_field
1 | jane | doe | | 1005 | secretvalue1
5 | jane | doe | m | 1060 | secretvalue1
3 | jon | doe | | 1010 | secretvalue2
4 | jon | doe | a | 3030 | secretvalue2


The duplicated member_id fields are removed. Additionally, there are rows where the the only thing that is different is the customer_id, I need these completely removed.

That is, if all of the rows with the same secret_field values have the same member_ids I would like those to not show at all but if the secret value is the same and there are rows with some of the same member_ids and some different memeber_ids I would want the distinct member_ids in this case.

I could work with a query that will give the distinct member ids where the secret value is the same, but there is more than one row.

Continue reading...
 
Status
Not open for further replies.
Top