469,903 Members | 1,945 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Other than UNION

Hello,

Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)
solution than UNION operator in this case. TIA.

-- DDL
CREATE TABLE #TMP (col varchar(10));

-- DML
insert into #TMP
values('A124');

insert into #TMP
values('A127');

insert into #TMP
values('A12728');

insert into #TMP
values('A17282');
insert into #TMP
values('BCD');

insert into #TMP
values('BCD');

insert into #TMP
values('CDSS');

insert into #TMP
values('DS');

insert into #TMP
values('YUUEI');

-- goal: get one row with col data starting with 'A' and distict rows
for the rest

select top 1 col
from #TMP
where col LIKE 'A%'

UNION

select distinct col
from #TMP
where col NOT LIKE 'A%'

Jul 23 '05 #1
4 1531
On 16 Mar 2005 11:19:33 -0800, NickName wrote:
Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)
solution than UNION operator in this case. TIA.


Hi Nick,

Well, using UNION ALL instead of UNION would be the obvious improvement.
Or you could use the following (completely different) approach. Test it
in your environment to see which version performs best.

select distinct col
from #TMP AS t1
where col not like 'A%'
or not exists (select *
from #TMP AS t2
where col like 'A%'
and t2.col < t1.col)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2
Thanks for the response, Hugo. I was not paying enough attention. The
"DISTINCT" key word is not necessary in previous posting because UNION
would return unique records anyway, as for UNION ALL, it does not seem
applicable here because it would return duplicate rows as well, which
was/is not desired.

Don

Hugo Kornelis wrote:
On 16 Mar 2005 11:19:33 -0800, NickName wrote:
Bear with me (not had much sleep last night), pls see following ddl,
dml and comments for what is desired, I don't have a problem getting
desired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.
Hi Nick,

Well, using UNION ALL instead of UNION would be the obvious

improvement. Or you could use the following (completely different) approach. Test it in your environment to see which version performs best.

select distinct col
from #TMP AS t1
where col not like 'A%'
or not exists (select *
from #TMP AS t2
where col like 'A%'
and t2.col < t1.col)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)


Jul 23 '05 #3
On 16 Mar 2005 18:16:12 -0800, NickName wrote:
Thanks for the response, Hugo. I was not paying enough attention. The
"DISTINCT" key word is not necessary in previous posting because UNION
would return unique records anyway, as for UNION ALL, it does not seem
applicable here because it would return duplicate rows as well, which
was/is not desired.


Hi Don,

In your original code, your first query will always return one row, so
it can't have duplicates; the second uses DISTINCT to eliminate
duplicates. But since you don't use UNION ALL, the server still has to
check if the row from the first result set is also in the second result
set, and eliminate it if it is. You and I know that this can never
happen (as a result of mutual exclusive WHERRE clauses), so we can save
the server some work by adding ALL to the UNION keyword.

If you remove the DISTINCT from the second query, then you must indeed
use UNION, not UNION ALL. You still might to check if there's a big
difference in execution times (probably not, but you never know). Oh,
and do include my UNION-less version in your performance tests as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4
Hi Hugo, I appreciate your opinion. As my original posting indicated
"I was not in thinking mode when I composed the msg", for just now when
I took a closer look, I notice that the data set is small, hence,
almost "any" query would do the job well, not really necessarily for
optimization, Query Analyzer wasn't even able to calculate execution
time, however, for large data set, your other approach would be
meaningful to try. Again thanks.

Don

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Fred Hebert | last post: by
29 posts views Thread by Scott Marquardt | last post: by
6 posts views Thread by Neil Zanella | last post: by
73 posts views Thread by Sean Dolan | last post: by
4 posts views Thread by Girish | last post: by
10 posts views Thread by tapeesh | last post: by
18 posts views Thread by ranjeet.gupta | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.