473,728 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

No distinct in a select into stement ?

Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or AggregationTitl e4 is not NULL;
This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or
AggregationTitl e4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler
I

Apr 24 '06 #1
5 6894
Daniel Wetzler wrote:
Dear MSSQL experts,

I use MSSQL 2000 and encountered a strange problem wqhile I tried to
use a select into statement .

If I perform the command command below I get only one dataset which has
the described properties.
If I use the same statement in a select into statement (see the second
select) I get several datasets with the described properties like I
didn't use distinct
Is there any posiibility to use destinct in a select into statement

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
from Variables where Title1 is not NULL or Title2 is not NULL or
Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or AggregationTitl e4 is not NULL;
This is the same with select into :

select distinct IDENTITY (int) as ID, Title1 as Title1, Title2 as
Title2, Title3 as Title3,
AggregationTitl e1 as AggregationTitl e1, AggregationTitl e2 as
AggregationTitl e2,
AggregationTitl e3 as AggregationTitl e3, AggregationTitl e4 as
AggregationTitl e4
into VarTitles from Variables where Title1 is not NULL or Title2 is
not NULL or Title3 is not NULL or
AggregationTitl e1 is not NULL or AggregationTitl e2 is not NULL or
AggregationTitl e3 is not NULL or
AggregationTitl e4 is not NULL;

Hope anyone can help.

Best regards,

Daniel Wetzler


The IDENTITY function makes each row unique so DISTINCT doesn't
eliminate the duplicates in this case. Interestingly, this behaviour
seems to have changed in SQL Server 2005. If I run your SELECT INTO on
2005 I get a different execution plan with the IDENTITY value computed
after DISTINCT.

For 2000 the workaround is easy. The following should insert just one
row into vartitles.

CREATE TABLE variables (title1 VARCHAR(10) NULL, title2 VARCHAR(10)
NULL, title3 VARCHAR(10) NULL, aggregationtitl e1 VARCHAR(10) NULL,
aggregationtitl e2 VARCHAR(10) NULL, aggregationtitl e3 VARCHAR(10) NULL,
aggregationtitl e4 VARCHAR(10) NULL);

INSERT INTO variables VALUES ('1','1','1','1 ','1','1','1');
INSERT INTO variables VALUES ('1','1','1','1 ','1','1','1');

SELECT IDENTITY (INT) AS id,
title1, title2, title3, aggregationtitl e1, aggregationtitl e2,
aggregationtitl e3, aggregationtitl e4
INTO VarTitles
FROM (
SELECT DISTINCT
title1, title2, title3, aggregationtitl e1, aggregationtitl e2,
aggregationtitl e3, aggregationtitl e4
FROM variables
WHERE title1 IS NOT NULL
OR title2 IS NOT NULL
OR title3 IS NOT NULL
OR aggregationtitl e1 IS NOT NULL
OR aggregationtitl e2 IS NOT NULL
OR aggregationtitl e3 IS NOT NULL
OR aggregationtitl e4 IS NOT NULL) AS V ;

SELECT * FROM vartitles ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Apr 24 '06 #2
Thank you very much.
This was a very helpful hint.

Best regards,

Daniel

Apr 25 '06 #3
Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_tit le instead of this convoluted query.

Apr 25 '06 #4
> Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL
and.... bang goes performance too other than a probable clustered index scan
/ table scan.

Consider these two statements on my 800,000 row 834MByte message table for
the nntp forums....

There is a non-clustered index on nntp_author and there is non-clustered
index on author_id

-- Query 1
select count(*)
from mb_message_deta il
where nntp_author is not null
or author_id is not null

-- Query 2
select count(*)
from mb_message_deta il
where coalesce( nntp_author, author_id ) is not null

Query 1 will use the index author_id and give a half reasonable plan.
Query 2 will do a clustered index scan

Out of 100%, Query 1 is 12% and Query 2 is a whopping 88%

Seriously, go and get a junior job as a programmer and get some very needed
industrial / real world experience instead of bashing people down on here,
as far as 'newbie' goes - you have room to talk......

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@eart hlink.net> wrote in message
news:11******** *************@t 31g2000cwb.goog legroups.com... Minor trick to make the code easier to read and maintain:

WHERE COALESCE (title1, title2, title3) IS NOT NULL
OR COALESCE (ggregation_tit le1, aggregation_tit le2,
aggregation_tit le3, aggregation_tit le4) IS NOT NULL

Unfortunately these columns look like repeated and a really bad 1NF
problem. I have the feeling that you might have wanted to use
COALESCE() in the SELECT list to get a non-null title and non-null
aggregation_tit le instead of this convoluted query.

Apr 25 '06 #5
--------------------
Seriously, go and get a junior job as a programmer and get some very
needed
industrial / real world experience instead of bashing people down on
here,
as far as 'newbie' goes - you have room to talk......
--------------------

<BIG GRIN> from on who has been the receiving side of one of CELKO's
many put-downs.

Apr 26 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
11688
by: Martin Feuersteiner | last post by:
Dear Group I'm having trouble with the clause below. I would like to select only records with a distinct TransactionDate but somehow it still lists duplicates. I need to select the TransactionDate once as smalldatetime and once as varchar as I'm populating a drop-down with Text/Value pairs. So I can't just use 'SELECT DISTINCT TransactionDate FROM...' I'm grateful for any hints.
2
12545
by: mfyahya | last post by:
I have two tables, both containing an 'authors' column. Is there a way to get a unique list of authors from the two tables? I tried SELECT DISTINCT `authors` from `table1`, `table2`; but I got an "Column 'authors' in field list is ambiguous" error. Is there also a query to return only the count of distinct authors from the two tables? Thanks for any help.
9
10899
by: Kelvin | last post by:
Okay so this is baking my noodle. I want to select all the attritbutes/fields from a table but then to excluded any row in which a single attributes data has been duplicated. I.E. Here's my table:- ID Ref Name DATE 1 AAA Joe 1/2 2 BBB Ken 1/2 3 AAA Len 6/3
18
3055
by: mathilda | last post by:
My boss has been adamant that SELECT DISTINCT is a faster query than SELECT all other factors being equal. I disagree. We are linking an Access front end to a SQL Server back end and normally are only returning one record. She states that with disctinct the query stops as soon as it finds a matching record. Both of us are relative novices in database theory (obviously). Can someone help settle this?
1
14492
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT aggregate function, but there is no reference, at least that I can find anywhere, of how to do this. I have multiple lines fields for which I would like to do a "count distinct", but for simplicity, I am showing an example of only one field. Here is...
1
4089
by: Patrick.O.Ige | last post by:
I have a xml file and i want to format it using XSL My XSL file and XML below I needed to do a distinct which is ok on the first node "Code" For the "programDescription" i did below which gets the Count of the nodes and i get the programDescription node but it duplicates for the selected "Code" Node. So if the Count is 3 its shows values "Crazy Training 2" 3 times for Code "PRG004" Any help?
6
13568
by: Bob Stearns | last post by:
I am getting unwanted duplicate rows in my result set, so I added the DISTINCT keyword to my outermost SELECT. My working query then returned the following message: DB2 SQL error: SQLCODE: -214, SQLSTATE: 42822, SQLERRMC: CASE...;ORDER BY;2 Message: An expression in the ORDER BY clause in the following position, or starting with "CASE..." in the "ORDER BY" clause is not valid. Reason code = "2".  More exceptions ... DB2 SQL error:...
1
2597
by: Bill | last post by:
I'm trying to write a query that will select a distinct count of more than one field. I have records that display user productivity. Each of the records have a time associated with it and I want to be able to tell the distinct count of the products numbers and of the pallets ID that they worked with. Do need to work out some sort of sub-query to do this?
2
5341
by: Techhead | last post by:
I need to run a SELECT DISTINCT query across multiple fields, but I need to add another field that is NON-DISTINCT to my record set. Here is my query: SELECT DISTINCT lastname, firstname, middleinitial, address1, address2, city, state, zip, age, gender FROM gpresults
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8753
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9409
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9263
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
6704
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4522
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4787
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2642
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2159
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.