CODEWITHSUNDEEP
sqlpostgresqlunion
Luffydude
Luffydude

Reputation: 772

How to obtain a single result from a union of the same data with different conditions

So I have a union of various queries representing various conditions in order to reach my end goal, which is a list of entries according to their priority. This priority is given according to the

My query is 

select priority, priority_description, name_of_entry, id_of_entry from (
select 0 priority, 'text' priority_description, name_of_entry, id_of_entry from table_a where *conditions for priority 0*
union select 1 priority, 'text' priority_description, name_of_entry, id_of_entry from table_a where *conditions for priority 1*
union select 2 priority, 'text' priority_description, name_of_entry, id_of_entry from table_a, tableb where *conditions for priority 2*
union select 3 priority, 'text' priority_description, name_of_entry, id_of_entry from table_a, tablec where *conditions for priority 3*
) x

The numbers and text for 1st and 2nd columns are manually entered while the others are what I want to retrieve.

The intended result is to only show the entry with the least priority if it's replicated. In other words, if an entry fulfils multiple conditions, thus appearing in priority 2 and 3, I only want the record to be shown once with priority 2.

One idea would be for example to add another condition "and where not exists in (previous condition), however this might make everything way too slow since it would be repeating queries multiple times.

How to achieve this in a better way?

Upvotes: 0

Views: 73

Answers (3)

ravioli
ravioli

Reputation: 3823

Here's another option: 

SELECT priority, priority_description, name_of_entry, id_of_entry
FROM (
  SELECT priority, priority_description, name_of_entry, id_of_entry,
    ROW_NUMBER() OVER(
      PARTITION BY id_of_entry -- Group rows by "id_of_entry"
      ORDER BY priority -- Assign "ranking" based on lowest priority 
    ) AS RowNum 
  FROM (
    SELECT
      CASE
        WHEN <conditions for priority 0> THEN 0 -- Look for priority 0 first
        WHEN <conditions for priority 1> THEN 1
        WHEN <conditions for priority 2> THEN 2
        WHEN <conditions for priority 3> THEN 3
      END AS priority,
      'text' AS priority_description,
      name_of_entry,
      id_of_entry
    FROM table_a
  ) src
) src
WHERE RowNum = 1 -- Get single rows with lowest priority

This assumes the same id_of_entry value can have multiple rows with different priority conditions.

Also, how do you handle cases when there are multiple rows with the same id_of_entry and priority values? As written, the query won't discriminate with regards to which row to return.

Upvotes: 1

wildplasser
wildplasser

Reputation: 44230

You only select from table_a, with conditions on table[abc],so you can rewrite into:

SELECT COALESCE(zero.priority,one.priority,two.priority,three.priority)
        AS  priority
        , a.name_of_entry, a.id_of_entry
        , ...
FROM table_a a
LEFT JOIN (
        select 0 priority, id_of_entry
        from table_a
        where *conditions for priority 0*
        ) zero ON zero.id_of_entry = a.id_of_entry
LEFT JOIN (
        select 1 priority, id_of_entry
        from table_a
        where *conditions for priority 1*
        ) one ON one.id_of_entry = a.id_of_entry
LEFT JOIN (
        select 2 priority, id_of_entry
        from table_a
        WHERE EXISTS(
                SELECT*FROM table_b WHERE [table_a,table_b]
                AND *conditions for priority 2*)
        ) two ON two.id_of_entry = a.id_of_entry
LEFT JOIN (
        select 3 priority, id_of_entry
        from table_a
        WHERE EXISTS(
                SELECT*FROM table_c WHERE [table_a,table_c]
                AND *conditions for priority 3*)
        ) three ON three.id_of_entry = a.id_of_entry
        ;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use distinct on:

with q as (<your query here>)
select distinct on (id_entry) q.*
from q
order by id_entry, priority;

That said, you can probably just use a single case expression for this. It is a little complicated because you have two values. One method is something like this:

select v.priority,
       (case when v.priority = 0 then 'text0'
             when v.priority = 1 then 'text1'
             when v.priority = 2 then 'text2'
             when v.priority = 3 then 'text3'
        end) as priority_description,
        name_of_entry, id_of_entry
from table_a a cross join lateral
     (values (case when *conditions for priority 0* then 0
                   when *conditions for priority 1* then 1
                   when *conditions for priority 2* then 2
                   when *conditions for priority 3* then 3
              end)
      ) v(priority)

You can add in the

Upvotes: 1

Related Questions