Need workaround -can't use ORDER BY in UNION statement 2

[GabeC]

I need to run three queries and end up with one recordset (to be used via ADO.Net in an ASP.Net page).

(select * from employee_email where LName = 'adams' order by LName, FName
union
select * from employee_email where LName like 'ada%'
and LName <> 'adams' order by LName, FName)
union
(select * from employee_email where LName like '%adams%'
and LName <> 'adams' and LName not like 'ada%'
order by LName, FName)

If I leave out the Order By clauses, this returns the data I need but not in the order I need it.

Here is what I am trying to accomplish:

I want a record set with all the data from these three select queries but it needs to be ordered in the following way.

First all records where the last name is equal to the input name (in this case Adams).

Second, all records where the last name starts with the first three letters of the input name but not any records found in the first query.

Third, all records where the last name has the input name anywhere in the name except the records found in the first two queries.

How can I write the sql or stored procedure to accomplish this? I am using SQL Server 2000.





Thanks,

Gabe

 

[SQLDenis]

select *,1 as orderby from employee_email where LName = 'adams'
union
select * from employee_email,2 as orderby where LName like 'ada%'
and LName <> 'adams'
union
select *,3 as orderby from employee_email where LName like '%adams%'
and LName <> 'adams' and LName not like 'ada%'
order by Orderby,LName, FName

If you want all records returned use UNION ALL if you want distinct use UNION only


“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

OOPS should be
select *,1 as orderby from employee_email where LName = 'adams'
union
select *,2 as orderby from employee_emailwhere LName like 'ada%'
and LName <> 'adams'
union
select *,3 as orderby from employee_email where LName like '%adams%'
and LName <> 'adams' and LName not like 'ada%'
order by Orderby,LName, FName

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[GabeC]

Wow. How simple is that.

I really helps me out. Thanks!!!

Thanks,

Gabe

 

[r937]

SQLDenis, change your UNIONs to UNION ALL since there is no possibility of duplicate rows across the three subselects, so no need to have the extra sort

here's an alternate solution that makes only one pass of the data --

select *
  from employee_email 
 where LName like 'ada%'
    or LName like '%adams%'  
order
    by case when LName = 'adams' 
            then 1
            when LName like 'ada%'
             and LName <> 'adams' 
            then 2
            else 3
        end
     , LName
     , FName

r937.com | rudy.ca

 

[SQLDenis]

r937 I did mention UNION ALL in my first reply
And I added the OrderBy integer so that you can distinguish the 3 different results

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[r937]

my point was that if you insert 1,2,3 into those selects, there is no possibility that UNION will remove anything, so "if you want distinct use UNION only" does not actually work, and adds an extra unnecessary sort




r937.com | rudy.ca

 

[SQLDenis]

r937 yes you are right I overlooked that part (shame on me)



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[r937]

no prob

gabe, please let me know if my solution also works

r937.com | rudy.ca

 

[GabeC]

Both solutions work great. With SQLDenis' solution I can easily figure out what it is I am selecting. I didn't put it in my original post but there are actually 7 - 10 different select statements that I need to get data from.

I can then convert it into the r937 format once I have my thoughts collected.

Thanks to you both.



Thanks,

Gabe