Other than UNION

[NickName]

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%'

[Hugo Kornelis]

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)

[NickName]

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)

[Hugo Kornelis]

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)

[NickName]

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