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'
Tweet