Get result from second select query when first select query returns 0

Question

I have following schema

CREATE TABLE BookImage (Id UNSIGNED BIG INT, ImageId UNSIGNED BIG INT, IsDefault INT, 
                           PRIMARY KEY(Id, ImageId));

INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (1,10,0);
INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (1,11,1);
INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (1,12,0);

INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (2,20,0);
INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (2,21,0);
INSERT OR IGNORE INTO BookImage(Id,ImageId,IsDefault) VALUES (2,22,0);

My target is to get a SELECT query to find (ImageId, Id) for given "Id" such that-

1. if IsDefault=1, then return the row
2. if IsDefault=0, then return the row of lowest ImageId

For given table, if I query with Id=1, it should return only (11,1) if I query with Id=2, it should return only (20,2) because there is not IsDefault=1 for Id=2.

I need a sqlite select query to achieve that. I have tried the query from this post but it is not working

SELECT * 
FROM BookImage 
WHERE Id=1 AND IsDefault=1
UNION ALL
SELECT * 
FROM BookImage 
WHERE Id=1 ORDER BY ImageId ASC LIMIT 1 AND NOT EXISTS (
    SELECT *
    FROM BookImage 
    WHERE Id=1 AND IsDefault=1 LIMIT 1 
)

http://sqlfiddle.com/#!5/e094e/1/0

Answer 1

With ROW_NUMBER() window function:

SELECT Id, ImageId
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY Id ORDER BY IsDefault DESC, ImageId) rn
  FROM BookImage
) b
WHERE rn = 1 AND Id = ?

Replace ? with the Id that you search for.
See the demo.

Another way:

SELECT Id, ImageId
FROM BookImage
WHERE Id = ?
ORDER BY ROW_NUMBER() OVER (ORDER BY IsDefault DESC, ImageId)
LIMIT 1

See the demo.

For older versions of SQLite that do not support window functions:

SELECT Id, ImageId 
FROM BookImage
WHERE Id = ?
ORDER BY IsDefault DESC, ImageId
LIMIT 1;

See the demo.
Results for Id = 1:

| Id  | ImageId |
| --- | ------- |
| 1   | 11      |

Results for Id = 2:

| Id  | ImageId |
| --- | ------- |
| 2   | 20      |