Reputation: 143
I have a table which contains ID column:
ID
---
1
2
3
4
5
I have to retrieve an ID using a simple query. If the ID is not present in the table, then I want to display ID - 1
For example, In the above table, there is no ID = 7. So if I query something like:
SET @id = 7
SELECT COALESCE(id, @id - 1)
FROM table
WHERE id = @id
I understand it gives me NULL because the condition WHERE id = 7
do not match.
Here are the conditions I want to cover:
Is there any other approach I can use to get my desired output?
Upvotes: 0
Views: 78
Reputation: 175944
Using UNION ALL
:
WITH cte AS (
SELECT id FROM tab WHERE id = @id
)
SELECT id FROM cte
UNION ALL
SELECT @id - 1 WHERE NOT EXISTS (SELECT * FROM cte);
Upvotes: 1
Reputation: 17925
Using MAX()
will let you either keep the id found in the table and otherwise return the computed value. If you didn't use an aggregate function then there wouldn't be any table rows to return when the id doesn't match anything.
SET @id = 7
SELECT COALESCE(MAX(id), @id - 1)
FROM table
WHERE id = @id
Upvotes: 1
Reputation: 222582
You can left join your table with a fixed scalar resultset:
select coalesce(t.id, v.id - 1) id
from (values (7)) v(id)
left join mytable t on t.id = v.id
Upvotes: 1
Reputation: 247235
Use a subselect to always get a result:
SELECT coalesce(
(SELECT id FROM atable WHERE id = 7),
-1
);
Upvotes: 1