Select id of user for specific email, or max(id) 1 if email doesn't exist-CodePudding

I want to get the id of a user for a given email, or if the email doesn't exist, I want it to return MAX(id) 1.

So for the example below, I want:

For email '[email protected]', I want it to return 1
For email '[email protected]', I want it to return 9

I made an attempt but I couldn't make it work the way I want.

DECLARE @USERS TABLE (id int, name varchar(50), email varchar(50));

INSERT INTO @USERS(id, name, email) VALUES
(1, 'john', '[email protected]'),
(2, 'nick', '[email protected]'),
(3, 'alex', '[email protected]'),
(4, 'liam', '[email protected]'),
(5, 'noah', '[email protected]'),
(6, 'oliver', '[email protected]'),
(7, 'james', '[email protected]'),
(8, 'rob', '[email protected]');

SELECT CASE WHEN (ISNULL(id, 0) = 0) THEN id ELSE (MAX(id)   1)
FROM @USERS
WHERE email = '[email protected]';

CodePudding user response：

Two sub-queries inside a coalesce should do the job.

DECLARE @TestEmail VARCHAR(50) = '[email protected]';

SELECT 
    COALESCE((SELECT TOP 1 id FROM @USERS WHERE email = @TestEmail), (SELECT MAX(id)   1 FROM @USERS), 1);

This will only work in a simple scenario when you don't call this concurrently, and you make use of the returned ID before you would ever call this again. Otherwise you would need to perform some locking to ensure it works as expected.

CodePudding user response：

The following should satisfy syntax requirements:

select coalesce(max(id), (select coalesce(max(id), 0)   1 from @users))
from @users
where email = '[email protected]'

That being said, the max id could change between selecting and using it in another query.