Combine two sql select queries (in postgres) with LIMIT statement

Question

I've got a table and I want a query that returns the last 10 records created plus the record who's id is x.

I'm trying to do -

SELECT * FROM catalog_productimage
ORDER BY date_modified
LIMIT 10
UNION
SELECT * FROM catalog_productimage
WHERE id=5;

But it doesn't look like I can put LIMIT in there before UNION. I've tried adding another column and using it for sorting -

SELECT id, date_modified, IF(false, 1, 0) as priority FROM catalog_productimage
UNION
SELECT, id, date_modified, IF(true, 1, 0) as priority FROM catalog_productimage
WHERE id=5
ORDER BY priority, date_modified
LIMIT 10;

but I'm not making much progress..

score 87 · Accepted Answer · answered Nov 27 '12 at 12:28

87

Just checked that this will work:

(SELECT * FROM catalog_productimage
ORDER BY date_modified
LIMIT 10)
UNION
SELECT * FROM catalog_productimage
WHERE id=5;

answered Nov 27 '12 at 12:28

sufleR

2,865
17
31

4

Round brackets are required because `UNION` is actually a part of select statement and select statement syntax is such that `LIMIT` must come after `UNION`. So you need to explicitly end first expression. Also because of this syntax you to use `LIMIT` at the end of union. This way `LIMIT` would be applied to outcome of the union. See [Select documentation](https://www.postgresql.org/docs/current/sql-select.html) for more details. – Mr. Deathless Jul 20 '21 at 11:37
So if we had 3 untions to do, would this require a new set of paranthesis for each untion? – Adam Hughes Aug 01 '22 at 15:55

score 1 · Answer 2 · answered Nov 27 '12 at 12:26

This will give you records from 10th to 20th and should get you started.i will reply back with SQLfiddle

SELECT *  
  FROM (SELECT ROW_NUMBER () OVER (ORDER BY cat_id) cat_row_no, a.* FROM catalog_productimage a where x=5)  
 WHERE cat_row_no > 10 and cat_row_no <20

Combine two sql select queries (in postgres) with LIMIT statement

2 Answers2