Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL: Making an ugly query better

Posted on 2009-07-02
4
Medium Priority
?
316 Views
Last Modified: 2012-06-27
I can usually get by when it comes to SQL, but I'm no whiz. In the example I've included, the task is to end up with a list of:

* all search records that match the user's search criterion (lastname starts with T), OR
* that have email addresses matching records in the first group.

I have a query that seems (after minimal testing) to work. First I get the name matches; then supplement my results with the matching non-blank emails; then, for cases of multiple identical emails, I show only the record with the best (lowest) Priority score.

My question is: is there a more concise way to rephrase this query? (I don't have the option to restructure the database.) Something that left me with about three less WITH's -- i.e., a lone query -- would be ideal, but any improvement would be good.

WITH
NarrowedTable AS
(select id, email from tmp_test
where (lastname like 't%')),
 
ExtraEmailsTable AS
(select id from NarrowedTable
union
select id from tmp_test tt
where (coalesce(tt.email, '') > '')
and (tt.email in (select email from narrowedtable))),
 
InnerTable AS(
SELECT row_number() over(partition by email order by priority) as myrank,
id,
email,
lastname
from tmp_test tt
where (coalesce(Email, '') > '')
and (tt.id in (select id from ExtraEmailsTable))
union
SELECT 1 as myrank,
id,
email,
lastname
from tmp_test tt
where (coalesce(Email, '') = '')
and (tt.id in (select id from ExtraEmailsTable)))
 
select * from InnerTable
where myrank = 1
order by Email, lastname;

Open in new window

0
Comment
Question by:180246
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 668 total points
ID: 24768276
seems fine to me, except to put some comments into the code, and to use UNION ALL instead of UNION to avoid the implicit Distinct...
0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 668 total points
ID: 24768432
I agree with angelIII that it seems fine logically.  Getting rid of some of your CTE "withs" will add to the complexity of the final query by having to do extra subselects or joins there so that is matter of oppion if that is a "better" query or not.  If something in the query is not performing right or too slow then if you share some table structure and sample data that is causing a problem we could evaluate it better.
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 664 total points
ID: 24769095
The WITHs are good - they modularise your query nicely.

As the others say, there are other ways to improve the query, but you should embrace the WITH. I've helped many complex queries by introduce a bunch of CTEs.

Rob
0
 

Author Closing Comment

by:180246
ID: 31599388
Thank you all for the feedback! It's good to know I wasn't too far out in left field. I'm not aware of any performance problems with it at this point -- just figured that since I was using some features I haven't touched before, I should get a sanity check. I hope an even split of points (as even as I could make it) will be OK with you all.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question