Question 113186 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – FIltering result from query

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
Tags:

2

Answers


  1. 0

    Change

    WHERE s.id_utv = 10

    To

    WHERE s.id_utv = 10 AND INSTR(au.surename,'(old’) = 0

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search