Union query distinct on one column

Question

I would like results from the second query to override results from the first query:

SELECT "panel_restaurants_restaurant"."id",
       "panel_restaurants_restaurant"."name",
       "panel_restaurants_restaurant"."logo",
       "panel_restaurants_restaurantfeatures"."currency" AS "currency",
       ST_DistanceSphere(location, ST_GeomFromText('POINT(0.0 0.0)',4326)) AS "distance",
       "panel_meals_meal"."id" AS "meal_id",
       "panel_meals_meal"."status" AS "meal_status",
       "panel_meals_meal"."available_count" AS "available_dishes",
       "panel_meals_meal"."discount_price" AS "discount_price",
       "panel_meals_meal"."normal_price" AS "normal_price",
       "panel_meals_meal"."collection_from" AS "pickup_from",
       "panel_meals_meal"."collection_to" AS "pickup_to",
       "panel_meals_meal"."description" AS "meal_description"
FROM "panel_restaurants_restaurant"
INNER JOIN "panel_restaurants_restaurantfeatures" ON (
    "panel_restaurants_restaurantfeatures"."restaurant_id" = "panel_restaurants_restaurant"."id")
LEFT OUTER JOIN "panel_meals_meal" ON ("panel_restaurants_restaurant"."id" = "panel_meals_meal"."restaurant_id"
                AND "panel_meals_meal"."status" = 0
                AND (
                ("panel_meals_meal"."collection_from" AT TIME ZONE 'Europe/Warsaw')::date = DATE 'today' OR
                ("panel_meals_meal"."collection_from" AT TIME ZONE 'Europe/Warsaw')::date = DATE 'tomorrow'
                )
                AND "panel_meals_meal"."collection_to" > '2017-07-29 19:33:47.992075+00:00'
                AND "panel_meals_meal"."available_count" > 0)
WHERE "panel_restaurants_restaurant"."status" = 2
UNION
SELECT "panel_restaurants_restaurant"."id",
       "panel_restaurants_restaurant"."name",
       "panel_restaurants_restaurant"."logo",
       "panel_restaurants_restaurantfeatures"."currency" AS "currency",
       ST_DistanceSphere(location, ST_GeomFromText('POINT(0.0 0.0)',4326)) AS "distance",
       "panel_meals_meal"."id" AS "meal_id",
       "panel_meals_meal"."status" AS "meal_status",
       "panel_meals_meal"."initial_count" AS "available_dishes",
       "panel_meals_meal"."discount_price" AS "discount_price",
       "panel_meals_meal"."normal_price" AS "normal_price",
       "panel_meals_meal"."collection_from" AS "pickup_from",
       "panel_meals_meal"."collection_to" AS "pickup_to",
       "panel_meals_meal"."description" AS "meal_description"
FROM "panel_restaurants_restaurant"
INNER JOIN "panel_restaurants_restaurantfeatures" ON (
       "panel_restaurants_restaurantfeatures"."restaurant_id" = "panel_restaurants_restaurant"."id")
LEFT OUTER JOIN "panel_meals_meal" ON (
    "panel_restaurants_restaurant"."id" = "panel_meals_meal"."restaurant_id" AND
    "panel_meals_meal"."status" = 0)
INNER JOIN "panel_meals_mealrepeater" ON (
    "panel_meals_mealrepeater"."meal_id" = "panel_meals_meal"."id")
WHERE "panel_restaurants_restaurant"."status" = 2    AND "panel_meals_mealrepeater"."saturday" = true
ORDER BY distance ASC

For example - the first query may return nulls for what comes from the panel_meals_meal table, but the second one will return something - in that situation I will have the same values for id, name, logo, currency, distance and different values (nulls returned from the first query, and something from the other one) for all the other ones.

So the question is - how do I make this UNION distinct on a certain range of columns (actually only one would suffice - id)?

user_368691 · Answer

Using DISTINCT ON, e.g.

SELECT DISTINCT ON (maintenance_task_id)
  maintenance_task_id,
  execution_count
FROM (
  SELECT
    id maintenance_task_id,
    0 execution_count
  FROM maintenance_task
  UNION
  SELECT
    mte1.maintenance_task_id,
    count(*) execution_count
  FROM maintenance_task_execution mte1
  WHERE
    mte1.ended_at IS NULL
  GROUP BY mte1.maintenance_task_id
) AS t
ORDER BY
  maintenance_task_id,
  execution_count DESC

In this query:

UNION combines results of two queries.
DISTINCT ON picks one row from the top (based on the ORDER BY) for each unique maintenance_task_id value.

user_222135 · Answer

I did something similar by using a WITH query/CTE:

WITH override_query AS (SELECT * FROM blah_blah JOIN blah_blah [etc]),
     first_query AS (SELECT * FROM blah_blah JOIN blah_bluh [etc]
                    WHERE id NOT IN (SELECT id FROM override_query))
TABLE first_query UNION TABLE override_query

Union query distinct on one column

3 Answers