Solved

T-SQL: Making an ugly query better

Posted on 2009-07-02
4
299 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
Comment Utility
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 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 167 total points
Comment Utility
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 14

Assisted Solution

by:rob_farley
rob_farley earned 166 total points
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now