PostgreSQL: How to sort union query on not included column?

Question

I try to ORDER 2 UNIONed queries. Running this:

SELECT b.id
  FROM book.book b 
    WHERE title ILIKE '%something%' 
UNION
SELECT b.id
  FROM book.book b
    JOIN book.book_person bp
      ON bp.bookID = b.id 
    JOIN person p 
      ON p.id = bp.personID 
    WHERE lastname ILIKE '%something%' 
    ORDER BY b.title ASC, b.year DESC, b.volume ASC

gives me error:

ERROR:  42P01: missing FROM-clause entry for table "b"
LINE 12:         ORDER BY b.title ASC, b.year DESC, b.volume ASC
                          ^
LOCATION:  errorMissingRTE, parse_relation.c:3140

Without ORDER-clause it works fine. And it works fine when I include cols I want to ordered by:

SELECT b.id, b.title, b.year, b.volume 
  FROM book.book b 
    WHERE title ILIKE '%something%' 
UNION
SELECT b.id, b.title, b.year, b.volume 
  FROM book.book b
    JOIN book.book_person bp
      ON bp.bookID = b.id 
    JOIN person p 
      ON p.id = bp.personID 
    WHERE lastname ILIKE '%something%' 
    ORDER BY "title" ASC, "year" DESC, "volume" ASC

Is there better way to order UNIONed queris than include more columns?

eurotrash's & my answers are a bit different because i think we had different understandings of what your last query was trying to do - are you trying to have all title matches, then all last name matches in (title asc, year desc, volume asc) order, or to get all title & last name matches and then do the title/year/volume sort? (I assumed the latter) — AdamKG
– AdamKG, Commented Oct 16, 2018 at 17:24
@AdamKG: you assumed correctly, I search for 'something' inside title or person lastname and I need all book's IDs (directly from book.book or related to person) sorted by title, year and volume. — w.k
– w.k, Commented Oct 16, 2018 at 18:01

404 · Accepted Answer · 2018-10-16 17:21:42Z

1

That's because first the UNION result is created, then the ORDER BY is performed. title etc is no longer available to be references from the UNION result. (Basically UNION binds more tightly than ORDER BY.)

So to get around it just put brackets around the second query and the ORDER BY statement, assuming you only want to order that part:

SELECT id
...
UNION
(SELECT id
...
ORDER BY title, etc)

If you want the full query to be ordered, your UNION'd query will have to return all the ordering columns, then you'll do a select on that:

SELECT id
FROM (
    SELECT id, title, etc
    ...
    UNION
    SELECT id, title, etc
) x
ORDER BY title, etc

answered Oct 16, 2018 at 17:21

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AdamKG · Accepted Answer · 2018-10-16 17:22:19Z

That's the correct way to do it; if you need to have only the id output, just wrap your existing query with a select id from (...)_.

The reason you need to include columns you want to sort on in your union selects is, per the docs:

select_statement is any SELECT statement without an ORDER BY, LIMIT, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, or FOR KEY SHARE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

So the ORDER BY applies only to the result of the union, which, when only the book ID is output, only has that column available. So yeah, to have both result sets sorted by those other columns, they have to be in the SELECT column lists of both sides of the UNION.

Gordon Linoff · Accepted Answer · 2018-10-16 17:36:07Z

0

How about getting rid of the union entirely?

SELECT b.id
FROM book.book b LEFT JOIN
     book.book_person bp
     ON bp.bookID = b.id LEFT JOIN
     person p 
     ON p.id = bp.personID 
WHERE b.title ILIKE '%something%' OR p.lastname ILIKE '%something%' 
ORDER BY "title" ASC, "year" DESC, "volume" ASC

answered Oct 16, 2018 at 17:36

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

4 Comments

w.k Over a year ago

This was my first approach, but then my query did not use indexes from either table, so I thought UNION way I can guarantee that indexes are used. Actual query is little bit more complicated, because ILIKE (and indexes) works on multiple columns.

Gordon Linoff Over a year ago

@w.k . . . This should use indexes for the joins. Standard (b-tree) indexes won't be used for the like comparisons, because the pattern starts with a wildcard.

w.k Over a year ago

Should it make difference, that I use on both cases gin-indexes?

Gordon Linoff Over a year ago

@w.k . . . GIN indexes can be used for like and ilike. That makes the union` approach more reasonable.

Collectives™ on Stack Overflow

PostgreSQL: How to sort union query on not included column?

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related