FIltering result from query

Question

This query return users from database MariaDB (MySql). I need to remove rows from result, where in surname is added "(old)" OR "- R". How to edit a query?

SELECT au.id,
       au.name,
       au.surname
FROM statements s 
JOIN aduser au ON au.id=s.id_usp
WHERE s.id_utv = 10
GROUP BY au.surname
ORDER BY au.surname ASC

From this result of query:

id	name	surname
124	Mike	Argle
221	Mike	Argle (old)
138	Lisa	Doe
126	Lisa	Doe (old)
123	John	Harris
135	John	Harris - R
324	Ann	Perez
329	Tiffani	Perez

To this result. Query should return this result:

id	name	surname
124	Mike	Argle
138	Lisa	Doe
123	John	Harris
324	Ann	Perez
329	Tiffani	Perez

Answer 1

You can use a ROW_NUMBER window function to select the first value among the name + ' - R'/' (old)' doubled values.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER(
                  PARTITION BY name, surname REGEXP name+' (- R|(old))'  
                  ORDER BY surname) AS rn
    FROM tab
)
SELECT id, name, surname
FROM cte
WHERE rn = 1

Check the demo here.

---

Your query would get updated as such:

WITH cte AS (
    SELECT au.id,
           au.name,
           au.surname,
           ROW_NUMBER() OVER(
               PARTITION BY au.name, au.surname REGEXP au.name+' (- R|(old))'  
               ORDER BY au.surname) AS rn
    FROM statements s 
    JOIN aduser au ON au.id=s.id_usp
    WHERE s.id_utv = 10
)
SELECT id, name, surname
FROM cte
WHERE rn = 1
ORDER BY surname ASC