PostgreSQL SELECT UNION showing the result of each separate subquery

user_7303848 0
Viewed 71

I have a query in which I use UNION to join two similar queries into one, in the results I would like the result of the first query to come before the results of the second query, however UNION by default brings the mixed results. This is my original query:

SELECT users.name, users.last_name
FROM users
WHERE users.name LIKE 'Paulo%'
UNION
SELECT users.name, users.last_name
FROM users
WHERE users.name LIKE '%Paulo%';

Resultado:

    name     | last_name 
-------------+-----------
 João Paulo  | Silva   ----> Second subquery
 Paulo       | Roberto ----> First subquery
 Pedro Paulo | Camargo ----> Second subquery

A possible solution that I found was to add an attribute in the subquery where I inform the position of the object, however this interfered with the UNION grouping causing the results to be duplicated

SELECT 1 as position, users.name, users.last_name
FROM users
WHERE users.name LIKE 'Paulo%'
UNION
SELECT 2 as position, users.name, users.last_name
FROM users
WHERE users.name LIKE '%Paulo%'
ORDER BY position;

Result:

 position |    name     | last_name 
----------+-------------+-----------
        1 | Paulo       | Roberto
        2 | João Paulo  | Silva
        2 | Paulo       | Roberto (Duplicate record)
        2 | Pedro Paulo | Camargo

Now I am in need of a solution to ignore duplicate records of the final result

postgresql sql
3 Answers

Your particular query could be simplified to

SELECT
    CASE WHEN users.name LIKE 'Paulo%' THEN 1 ELSE 2 END AS position,
    users.name, users.last_name
FROM users
WHERE users.name LIKE '%Paulo%'
ORDER BY position;

However if you need to operate with some more general query you could to use distinct on () clause:

SELECT DISTINCT ON (name, last_name)
    name, last_name, position
FROM (
    SELECT 1 as position, users.name, users.last_name
    FROM users
    WHERE users.name LIKE 'Paulo%'
    UNION ALL
    SELECT 2 as position, users.name, users.last_name
    FROM users
    WHERE users.name LIKE '%Paulo%') AS t
ORDER BY name, last_name, position;
user_5931440

you don't need a UNION. Every result that would come back from your first query will ALSO come back in your second query.

UPDATE:

I want to show first the names that start with the term and then the names that have the term anywhere

SELECT users.name, users.last_name
FROM users
WHERE users.name LIKE 'Paulo%'
order by position('Paulo' in users.name)
user_2346570

I'll use Postgresql to answer your question.

To ignore duplicate records of the final result, you will need ignore the results of first query, when get the second query. Can you use, Subquery to do this.

First example:

SELECT users.name, users.last_name 
FROM users 
WHERE users.name LIKE 'Paulo%'

UNION

SELECT users.name, users.last_name
FROM users 
WHERE users.name LIKE '%Paulo%'
AND users.id NOT IN ( SELECT id FROM users WHERE users.name LIKE 'Paulo%' )

Other alternative is use WithQueries to increase your performance.

Second example:

WITH query_cache_01 AS (
  SELECT users.id, users.name, users.last_name
  FROM users 
  WHERE users.name LIKE '%Paulo%'
), query_cache_02 AS (
  SELECT id, name, last_name
  FROM query_cache_01
  WHERE name LIKE 'Paulo%'
)

SELECT query_cache_02.name, query_cache_02.last_name 
FROM query_cache_02

UNION

SELECT query_cache_01.name, query_cache_01.last_name
FROM query_cache_01 
WHERE query_cache_01.id NOT IN ( SELECT id FROM query_cache_02 )

Reference: https://www.postgresql.org/docs/12/queries-with.html

user_43213490
Related