SQL UNION and ORDER BY

Question

I have an annoying SQL statement that seem simple but it looks awfull. I want the sql to return a resultset with userdata ordered so that a certain user is the first row in the resultset if that users emailaddress is in the companies table.

I have this SQL that returns what i want but i think it looks awful:

select 1 as o, * 
from Users u
where companyid = 1
and email = (select email from companies where id=1)
union 
select 2 as o, * 
from Users u
where companyid = 1
and email <> (select email from companies where id=1)
order by o

And by the way, the emailaddress from the user table can be in many companies so there cant be a join on the emailaddress :-(

Do you have any ideas how to improve that statement?

Im using Microsoft SQL Server 2000.

Edit: Im using this one:

select *, case when u.email=(select email from companies where Id=1) then 1 else 2 end AS SortMeFirst 
from Users u 
where u.companyId=1 
order by SortMeFirst

Its way more elegant than mine. Thanks Richard L!

Which RDBMS are you using (Oracle, SQLserver..) – FerranB Jan 28 '09 at 12:57 — FerranB, Jan 28 '09 at 12:57

score 6 · Accepted Answer · answered Jan 28 '09 at 13:02

6

You could do something like this..

        select CASE 
                WHEN exists (select email from companies c where c.Id = u.ID and c.Email = u.Email) THEN 1 
                ELSE 2 END as SortMeFirst,   * 
    From Users u 
    where companyId = 1 
    order by SortMeFirst

answered Jan 28 '09 at 13:02

Richard L

1,211
7
10

1

note that this isn't an optimal solution since your select in the case will run once for every returned row. this can potentialy cause a huge performance problem – Mladen Prajdic Jan 28 '09 at 16:07

score 5 · Answer 2 · answered Jan 28 '09 at 12:57

5

will this work?:

select c.email, * 
from Users u
     LEFT JOIN companies c on u.email = c.email
where companyid = 1
order by c.email desc
-- order by case when c.email is null then 0 else 1 end

answered Jan 28 '09 at 12:57

Mladen Prajdic

15,457
2
43
51

you need to add Users.companyid = Companies.companyid in the JOIN clause – devio Jan 28 '09 at 13:33

DanSingerman · Answer 3 · 2009-01-28T13:14:05.570

I am not sure this is better, but it is an alternative approach

select *, (select count(*) from companies where email = u.email) as o 
from users u 
order by o desc

Edit: if there can be many emails across different companies that match, and you are only interested in the given company, this becomes

select *, 
 (select count(*) from companies c where c.email = u.email and c.id = 1) as o 
from users u 
where companyid = 1
order by o desc

SQL UNION and ORDER BY

3 Answers3

Linked