Custom order #Postgres #SQL union queries

Consider the following data:

insert into users (email, first, last) values ('newuser@example.com', 'New', 'User');
insert into users (email, first, last) values ('notthereallemmy@example.com', 'Lemmy', 'Kilmister');
insert into users (email, first, last) values ('lemmy@example.com', 'Lemmy', 'Kilmister');

Assuming you want to match a user to the following data:

{ email: 'lemmy@example.com', first: 'New', last: 'User' }
````

And you consider email a better indication of a match, and first and last name are a fallback. The following query will not yield the desired results despite the `where` conditions being ordered per the requirements:

select * from users where email = 'lemmy@example.com' or (first = 'New' and last = 'User') limit 1;

This will result in:

1 | 'newuser@example.com' | 'New' | 'User'

To find by email first we will need to union two queries, rank the match then order by rank:

(select 1 as rank, id, email, first, last from users where email = 'lemmy@example.com') union (select 2 as rank, id, email, first, last from users where first = 'New' and last = 'User') order by rank limit 1;

This will yield the correct result:

3 | 'lemmy@example.com' | 'Lemmy' | 'Kilmister'