Help query distinct without union

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help query distinct without union

Author

Topic

santana
Yak Posting Veteran

72 Posts

Posted - 2010-01-27 : 03:24:41

Hi,

I have this:
SELECT Clients.Standard1, Clients.Standard2, Clients.Standard3, Clients.Standard4, Clients.Standard5 FROM Clients WHERE Clients.ClientRef="12";

|---------+---------+---------+---------+---------|
|Standard1|Standard2|Standard3|Standard4|Standard5|
|---------+---------+---------+---------+---------|
| | | | | |
|---------+---------+---------+---------+---------|
|19 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|19 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|19 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 |12 | |
|---------+---------+---------+---------+---------|
|30 |11 |32 | | |
|---------+---------+---------+---------+---------|
|30 |11 |32 | | |
|---------+---------+---------+---------+---------|

But I would like this:

|--------|
|Standard|
|--------|
|19 |
|--------|
|11 |
|--------|
|32 |
|--------|
|12 |
|--------|
|30 |
|--------|

How can I do this without UNION?

Thanks.

Regards,

santana

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 04:20:10

Why without UNION?

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-01-27 : 04:23:42

Because I need other way faster.
is it possible?

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:30:48

I've never found UNION to be slow. You just get N queries, each is optimised.

You could change the design of your database so it was Normalilsed, that would make for a faster query.

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 04:31:29

P.S. Indexes on each of the 5 columns would help a UNION query.

santana
Yak Posting Veteran

72 Posts

Posted - 2010-01-27 : 05:20:27

The only way for this, is with union?

thanks for all!!!!

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 05:36:01

Have you tried UNION?

Is it slow?

You have four columns in one table that you want to treat as if they were in a sub table. Either change your database structure, use a UNION, tell me the UNION query is too slow and we can see if we can optimise it, or write to Microsoft and get them to change the way that Relational Databases work.

Just asking me if there is a way of doing it without using UNIONS, in the absence of some information about HOW slow it is or WHY it doesn't work for you, is just wasting folks time here.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-27 : 05:59:49

There is another way to do it, but really troublesome. It involves normalising the table via a temporary table. This is a LOT less efficient then Union. I strongly recommend Union.

-- Create a Temp Table
Declare @tempTable Table
(standard int
clientID int)

-- Insert the data row by row into @tempTable
Insert into @tempTable
Select standard1, clientRef
from Clients

Insert into @tempTable
Select standard2, clientRef
from Clients

Insert into @tempTable
Select standard3, clientRef
from Clients

Insert into @tempTable
Select standard4, clientRef
from Clients

Insert into @tempTable
Select standard5, clientRef
from Clients

-- Now select from the normalised table
Select distinct standard from
@TempTable where clientRef =12

There are other solutions involving temp tables as well. But I suggest you use union as it is a lot easier and likely to be faster.

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 06:08:56

"It involves normalising the table via a temporary table."

hahaha! In which case better to normalise the table in the first place, I reckon!

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2010-01-27 : 06:14:01

Yes, definitely! ;)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 06:18:22

quote:
Originally posted by Kristen

"It involves normalising the table via a temporary table."

hahaha! In which case better to normalise the table in the first place, I reckon!

I agree with you. I think the above suggested would not be better than using UNION

Madhivanan

Failing to plan is Planning to fail

santana
Yak Posting Veteran

72 Posts

Posted - 2010-01-27 : 07:51:39

Thank you for all replay , and all help.

I decide with the client to use the union.

It is the best way.

Regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources