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..

Solution 1

Just checked that this will work:

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

Solution 2

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  

Comments

[Aidan Ewen]

I was so nearly there! Thanks!

[Sanjiv Jivan]

Thanks for this, odd that it wouldn't work without the brackets

[Mr. Deathless]

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 for more details.