Showing posts with label duplicates. Show all posts
Showing posts with label duplicates. Show all posts

Tuesday, March 27, 2012

DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

DISTINCT Values

I am trying to run a query to one of two delete duplicates records. The
process I normally use is use
1) SELECT DISTINCT from the table into a second table
2) Delete all duplicate values in original table
3) Copy the disctinct values from the second table back into the original
table
Unfortunately, this time, my table has ntext fields in it. SELECT DISTINCT
does not work with ntext fields.
Does anyone have an alternative solution?
Thank you,
JLFlemingHave you thought about declaring a PRIMARY KEY?|||I have thought about it. I cannot declare a primary key if there are alread
y
duplicates in the table. Once I get rid of duplicates, I can put a primary
key in.
"--CELKO--" wrote:

> Have you thought about declaring a PRIMARY KEY?
>|||http://www.aspfaq.com/2431
http://www.aspfaq.com/2509
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:FAF44802-6129-435D-B7AE-EBBC0C042ECD@.microsoft.com...
> I am trying to run a query to one of two delete duplicates records. The
> process I normally use is use
> 1) SELECT DISTINCT from the table into a second table
> 2) Delete all duplicate values in original table
> 3) Copy the disctinct values from the second table back into the original
> table
> Unfortunately, this time, my table has ntext fields in it. SELECT
> DISTINCT
> does not work with ntext fields.
> Does anyone have an alternative solution?
> Thank you,
> JLFleming

DISTINCT Value

if I have 5 columns that I want to check for distinct values in all of them
(not just one columns distinct values - I want to eliminate duplicates from
the entire returned result set) will
Select DISTINCT col1,col2,col3,col4,col5 from mytable order by myfieldname
do the trick?
Do you have people in your band or group that only play by ear?
When transposing songs do those that play by ear struggle and
does it leave vocals and band members without a recording in the
correct key to practice with?
Visit http://www.jerichoband.net/jericho/keychange.htm
where you can have the song's key changed to what you need
without negatively affecting the tempo or instruments in the recording...Select DISTINCT col1,col2,col3,col4,col5 from mytable order by
myfieldname
only elimantes the combination of these columns in the resultsset, is
it that what you want to achieve ?
HTH, jens Suessmeyer.|||Just to state this right:
it only elimantes the duplicate combinations.
-Jens.|||That is exactly what DISTINCT does.
ML
http://milambda.blogspot.com/

Sunday, March 25, 2012

DISTINCT returns duplicates

I use the DISTINCT keyword to avoid returning duplicates from the query
below (multivariable search for plants). However the result does contain
duplicates and I just can't figure out why. Please help!
TIA
CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
(
@.Value varchar(256) ,
@.CategoryID int ,
@.DescPlantHerb varchar(64) ,
@.AcclimatCanada varchar (64) ,
@.EnvCult varchar (145) ,
@.RareMenacee varchar (64) ,
@.PaysOrig int ,
@.PropTherapeut int ,
@.PropAutres int ,
@.Ingredients int
)
AS
set nocount on
SELECT DISTINCT
pTax.PlanteID ,
pTax.nomlatinscientifique ,
pTaxCat.CategoryID ,
pTax.descriptiontechniquebotanique ,
pCul.AcclimatationauCanada ,
pCul.environnementdeculture ,
pTax.raretemenacee ,
pPays.PaysID ,
pOPTH.ValueID ,
pOPA.ValueID ,
pOI.ValueID
FROM
PlantesTaxonomie pTax
left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
pTaxCat.PlanteID
left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
pTax.PlanteID
left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
pbChType.ID
left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID = pOpp.ID
left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
WHERE
pTax.nomlatinscientifique like '%'+ @.Value +'%'
or pTaxCat.CategoryID = @.CategoryID
or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
--and pCul.environnementdeculture= @.EnvCult
or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
or pPays.PaysID = @.PaysOrig
or pOPTH.ValueID = @.PropTherapeut
or pOPA.ValueID = @.PropAutres
or pOI.ValueID = @.Ingredients
set nocount off
GOWithout sample data, it's difficult to tell; however, I'd check to make
sure that the values you think are duplicates are actually duplicates.
Look for extra spaces or carriage returns at the end of your varchar
values.
HTH,
Stu|||You need to include your DDL.
http://www.aspfaq.com/etiquette.asp?id=5006
Without knowing your keys we cannot determine why you are getting
duplicates.
My guess is that you are not joining on your full keys for at least one
join.
"alto" <altodorov@.hotmail.com> wrote in message
news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> I use the DISTINCT keyword to avoid returning duplicates from the query
> below (multivariable search for plants). However the result does contain
> duplicates and I just can't figure out why. Please help!
> TIA
> CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
> (
> @.Value varchar(256) ,
> @.CategoryID int ,
> @.DescPlantHerb varchar(64) ,
> @.AcclimatCanada varchar (64) ,
> @.EnvCult varchar (145) ,
> @.RareMenacee varchar (64) ,
> @.PaysOrig int ,
> @.PropTherapeut int ,
> @.PropAutres int ,
> @.Ingredients int
> )
> AS
> set nocount on
> SELECT DISTINCT
> pTax.PlanteID ,
> pTax.nomlatinscientifique ,
> pTaxCat.CategoryID ,
> pTax.descriptiontechniquebotanique ,
> pCul.AcclimatationauCanada ,
> pCul.environnementdeculture ,
> pTax.raretemenacee ,
> pPays.PaysID ,
> pOPTH.ValueID ,
> pOPA.ValueID ,
> pOI.ValueID
> FROM
> PlantesTaxonomie pTax
> left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
> pTaxCat.PlanteID
> left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
> left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
> pTax.PlanteID
> left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
> pbChType.ID
> left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
> left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID =
pOpp.ID
> left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
> left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
> WHERE
> pTax.nomlatinscientifique like '%'+ @.Value +'%'
> or pTaxCat.CategoryID = @.CategoryID
> or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
> or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
> --and pCul.environnementdeculture= @.EnvCult
> or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
> or pPays.PaysID = @.PaysOrig
> or pOPTH.ValueID = @.PropTherapeut
> or pOPA.ValueID = @.PropAutres
> or pOI.ValueID = @.Ingredients
> set nocount off
> GO
>|||>I use the DISTINCT keyword to avoid returning duplicates from the query
>below (multivariable search for plants). However the result does contain
>duplicates and I just can't figure out why. Please help!
Are you sure for two rows that look like duplicates, that EVERY SINGLE
COLUMN is the same? Distinct applies to *every* column, not just the first
one.
A|||Ahhhh... I misunderstood the question. I thought the OP was trying to
remove dupes from the original query so that distinct would not be
necessary.
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1146163975.702036.262020@.u72g2000cwu.googlegroups.com...
> Without sample data, it's difficult to tell; however, I'd check to make
> sure that the values you think are duplicates are actually duplicates.
> Look for extra spaces or carriage returns at the end of your varchar
> values.
> HTH,
> Stu
>|||Absolutely, I can see the same PK several times in the resultset in Query
Analyzer.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
> Are you sure for two rows that look like duplicates, that EVERY SINGLE
> COLUMN is the same? Distinct applies to *every* column, not just the
> first one.
> A
>|||You should expect to get the same primary key multiple times because you are
joining many different tables. Some of these tables have more than one row
with that PK, because they are using it as a foreign key in a one to many
relationship.
As Aaron explained, look at all the other columns in your results, one of
them is different, which is what you are supposed to get.
"alto" <altodorov@.hotmail.com> wrote in message
news:uwRaO4iaGHA.4772@.TK2MSFTNGP05.phx.gbl...
> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message
> news:eZa8fyiaGHA.4580@.TK2MSFTNGP03.phx.gbl...
contain
>|||DISTINCT removes duplicates. If it appears that the query is
returning duplicates despite the DISTINCT there are two possibilities.
Most likely is that there is at least one difference that is not
immediately apparent. The other is a bug in SQL Server.
One issue that can't be ignored is that sometimes questions here that
involve DISTINCT (or GROUP BY) and "duplicates" are the result of a
misunderstanding of what DISTINCT does. DISTINCT operates on the
entire row. ANY difference in ANY column will result in seperate
rows.
One approach to narrowing down the problem is to play with the SELECT
list. Run the query - including the DISTINCT - specifying just one
column at a time in the SELECT list. Inspect the results closely,
looking for what appear to be duplicates. Apparent duplicates should
then be inspected with such tools as DATALENGTH and character by
character ASCII(substring()).
Roy Harvey
Beacon Falls, CT
On Thu, 27 Apr 2006 14:47:40 -0400, "alto" <altodorov@.hotmail.com>
wrote:

>I use the DISTINCT keyword to avoid returning duplicates from the query
>below (multivariable search for plants). However the result does contain
>duplicates and I just can't figure out why. Please help!
>TIA
>CREATE PROCEDURE dbo.usp_MultivarSearch_Plantes
>(
> @.Value varchar(256) ,
> @.CategoryID int ,
> @.DescPlantHerb varchar(64) ,
> @.AcclimatCanada varchar (64) ,
> @.EnvCult varchar (145) ,
> @.RareMenacee varchar (64) ,
> @.PaysOrig int ,
> @.PropTherapeut int ,
> @.PropAutres int ,
> @.Ingredients int
> )
>AS
>set nocount on
>SELECT DISTINCT
>pTax.PlanteID ,
> pTax.nomlatinscientifique ,
> pTaxCat.CategoryID ,
> pTax.descriptiontechniquebotanique ,
> pCul.AcclimatationauCanada ,
> pCul.environnementdeculture ,
> pTax.raretemenacee ,
> pPays.PaysID ,
> pOPTH.ValueID ,
> pOPA.ValueID ,
> pOI.ValueID
>FROM
>PlantesTaxonomie pTax
> left join PlantesTaxonomieCategory pTaxCat on pTax.PlanteID =
>pTaxCat.PlanteID
> left join PlantesCulture pCul on pCul.PlanteID = pTax.PlanteID
> left join PlantesBiochimieChimiotype pbChType on pbChType.PlanteID =
>pTax.PlanteID
> left join PlantesBiochimieChimiotypePays pPays on pPays.ChimiotypeID =
>pbChType.ID
> left join PlantesOpp pOpp on pOpp.PlanteID = pTax.PlanteID
> left join PlantesOppPropTherapeutique pOPTH on pOPTH.PlanteOppID = pOpp.I
D
> left join PlantesOppPropAutres pOPA on pOPA.PlanteOppID = pOpp.ID
> left join PlantesOppIngredients pOI on pOI.PlanteOppID = pOpp.ID
>WHERE
>pTax.nomlatinscientifique like '%'+ @.Value +'%'
> or pTaxCat.CategoryID = @.CategoryID
> or pTax.descriptiontechniquebotanique like '%'+ @.DescPlantHerb +'%'
> or pCul.AcclimatationauCanada like '%'+ @.AcclimatCanada +'%'
> --and pCul.environnementdeculture= @.EnvCult
> or pTax.raretemenacee like '%'+ @.RareMenacee +'%'
> or pPays.PaysID = @.PaysOrig
> or pOPTH.ValueID = @.PropTherapeut
> or pOPA.ValueID = @.PropAutres
> or pOI.ValueID = @.Ingredients
>set nocount off
>GO
>|||> Absolutely, I can see the same PK several times in the resultset in Query
> Analyzer.
Yes, is the PK the only column in your resultset? NO! DISTINCT applies to
ALL COLUMNS, not just the PK.
A|||Here:
DDL
============================
CREATE TABLE [PlantesTaxonomie] (
[PlanteID] [int] NOT NULL ,
[nomlatinusite] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomlatinscientifique] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[famille] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[groupe] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[auteur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunanglais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcommercial] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomcommunfranais] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassification] [varchar] (140) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cledeclassificationimage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[nomscommunsautres] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[synonymes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[formescitees] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[nomautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referencedeFloras] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[PFAFRating] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[epithete] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Nombredechromosomes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[identificationherbier] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontechniquebotanique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[taxonssemblables] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Cultivars] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[referenceautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[nomcycledevie] [varchar] (192) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[codealphabetique] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Typeplantes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptiondelafleur] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[couleurdelafleur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fleursramet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[inflorescence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[desriptiondesfeuilles] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[cotyledons] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Moisdefloraisondebut] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonfin] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Moisdefloraisonrecurrence] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Floraisondusemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fruitdescription] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[vigueurdesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[longevitedesemences] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptionracines] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiontronctiges] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmin] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmax] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypepHmoy] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[habitatbiotypeprecipitationmin] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmax] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypeprecipitationmoy] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatbiotypetemperature] [varchar] (5) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantehauteurmaximale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[hauteurminimale] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[plantelargeur] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Morphologieousilhouette] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[plantephysiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantevigueur] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[descriptionautresinformation] [text] COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[ensoleillementremarques] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[sourcegenetique] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[distributiongeographiqueabondance] [varchar] (144) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[pollenbiologieconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[semencessporebiologie] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[raretemenacee] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[particulariteautresinformation] [text] COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesTaxonomie_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesTaxonomie] PRIMARY KEY CLUSTERED
(
[PlanteID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesTaxonomie_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesCulture] (
[PlanteID] [int] NOT NULL ,
[Paysregionsdeculture] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Paysfournisseurs] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[origineautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Climatprefere] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[AcclimatationauCanada] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[climatautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[maladiesinsectes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[Exigencesdefertilisation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Preparationduterrain] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[rotation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[tauxdesemis] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[transplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fertilite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[agroautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[besoineneauarrosage] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[arrosagefrequence] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[humiditedusol] [varchar] (155) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[collectionourecoltesauvage] [varchar] (100) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[culturecomplet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureduree] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[engrais] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Exigencesdefertilisationculturale] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[essaisdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[espacement] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[distancedeplantation] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[plantationenpot] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[profondeurdeplantation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureinvitro] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresnotesdeculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[modedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[habitatendroit] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pH] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiondeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[sol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[qualitedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[texturedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[drainagedusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[structuredusol] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[niveauensoleillementrequis] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[zonederusticite] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[fichetechniqueagricole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Valeurculturale] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[besoinenmatiereorganique] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[environnementdeculture] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[controledeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[conditionsautres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[cultureenforet] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiedeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[regiephytosanitaire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Invasionparametrescontrole] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Restorationactivites] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Entretien] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[Environnementurbainadaptation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Programmedeconservation] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Problemesdeculture] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[fichetechniquehorti] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[Cultureautresnotes] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[culturedesemisennature] [varchar] (145) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[Maintenanceentretien] [varchar] (145) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[autresconditionsculture] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[datelastmodified] [datetime] NULL ,
[lastmodifiedby] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesCulture_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesCulture] PRIMARY KEY CLUSTERED
(
[PlanteID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesCulture_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesBiochimieChimiotype] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[constituants] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[teneurpourcentage] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[partieplante] [int] NULL ,
[partieplanteautre] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[descriptiondrogue] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[structuremoleculaire] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[usagespotentiels] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[extrait] [int] NULL ,
[indicederefraction] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pouvoirrotatoire] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[teneurenproduitspourcentage] [varchar] (64) COLLATE
SQL_Latin1_General_CP1_CI_AI NULL ,
[odeuretsaveur] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointebullition] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[pointdefusion] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[pointdecongelation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[pointdevaporisation] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AI
NULL ,
[solubilite] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autres] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsmicroorganismes] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[testsanimauxlaboratoire] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL
,
[essaiscliniques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[essaistoxicologiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotype] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesBiochimieChimiotype_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesBiochimieChimiotypePays] (
[ChimiotypeID] [int] NOT NULL ,
[PaysID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesBiochimieChimiotypePays] PRIMARY KEY CLUSTERED
(
[ChimiotypeID],
[PaysID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOpp] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PlanteID] [int] NOT NULL ,
[OppType] [int] NULL ,
[OppName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[UsageType] [int] NULL ,
[UsageTypeID] [int] NULL ,
[UsageOther] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[autresinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[aspecteconomiques] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[propintellectuelle] [text] COLLATE SQL_Latin1_General_CP1_CI_AI NULL ,
[DateLastModified] [datetime] NULL CONSTRAINT
[DF_PlantesOpp_DateLastModified] DEFAULT (getdate()),
[LastModifiedBy] [int] NULL ,
[IsDirty] [bit] NULL CONSTRAINT [DF_PlantesOpp_IsDirty] DEFAULT (1),
[ApprovedBy] [int] NULL ,
CONSTRAINT [PK_PlantesOpp] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY] ,
CONSTRAINT [FK_PlantesOpp_Plantes] FOREIGN KEY
(
[PlanteID]
) REFERENCES [Plantes] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [PlantesOppPropTherapeutique] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropTherapeutique] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOppPropAutres] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppPropAutres] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [PlantesOppIngredients] (
[PlanteOppID] [int] NOT NULL ,
[ValueID] [int] NOT NULL ,
CONSTRAINT [PK_PlantesOppIngredients] PRIMARY KEY CLUSTERED
(
[PlanteOppID],
[ValueID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
DATA
==============================
INSERT INTO [PlantesTaxonomie] VALUES(1,'Narthecium americanum','Abama
americana (Ker-Gawl.) Morong','Liliaceae','','','','','','',NU
LL,'','','','
','','','','','','','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','','','','',''
,'','
INSERT INTO [PlantesTaxonomie] VALUES(79144,'Trillium cernuum L.','Trillium
cernuum L.','Liliaceae','','','whip-poor-will
flower','','','',NULL,'','','',' ','','','','','','','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','','','',''
INSERT INTO [PlantesTaxonomie] VALUES(82756,'planta testae','planta
testae','','','','test plant','','plante de test','',NULL,'','','','
','','','','','','Description plante - herbier','','','
','','','','','','','','','','8','8','8'
,'','','','','','','','
INSERT INTO [PlantesCulture] VALUES(1,'','',' ','','','
','','','','','','','',' ','','','','','','','','','','','','',''
,'','
','3','','','','1','1','','','2','','1',
'','','','2','','
','','','','','3','','3','','','','','',
'',' ','Nov 18 2005 11:54:36:19
INSERT INTO [PlantesCulture] VALUES(82756,'','',' ','','bien acclimate','
','','','','','','','',' ','','','','','','','','','','','','',''
,'','
','3','','','','1','1','','','2','','1',
'','','','2','','
','','','','','3','','3','','','','','',
'',' ','Nov
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(1,1,'','',1,'','','','',1,'','','
','','','','','','',' ','Le
mthyleugnol a des proprits antimicrobiennes (Grifin et al., 1998).
Proprits nutritives L'lmicine et le mthyleugnol possdent des p
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(80,79142,'','',9,'ynhryeberyb',''
,'','',3,'','','','','','','','','',
'
',' ',' ',' ',' ')
INSERT INTO [PlantesBiochimieChimiotype]
VALUES(81,82756,'constituants...','10',9,'','description -
drogue...','','Usages potentiels...',3,'','','','','','','','','',' ',' ','
',' ',' ')
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,2)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(1,19)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(80,4)
INSERT INTO [PlantesBiochimieChimiotypePays] VALUES(81,36)
INSERT INTO [PlantesOpp] VALUES(18,79142,2,'fruit',-1,-1,'',' ',' ',' ','
','Nov 18 2005 3:01:55:547PM',48,1,NULL)
INSERT INTO [PlantesOpp] VALUES(19,82756,3,'opp test',-1,-1,'',' ',' ',' ','
','Nov 18 2005 4:28:34:680PM',2,1,NULL)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(1,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(5,1)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,3)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,7)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(13,11)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,5)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,9)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(14,12)
INSERT INTO [PlantesOppPropTherapeutique] VALUES(19,3)
INSERT INTO [PlantesOppPropAutres] VALUES(1,2)
INSERT INTO [PlantesOppPropAutres] VALUES(5,3)
INSERT INTO [PlantesOppPropAutres] VALUES(13,2)
INSERT INTO [PlantesOppPropAutres] VALUES(19,2)
INSERT INTO [PlantesOppIngredients] VALUES(1,2)
INSERT INTO [PlantesOppIngredients] VALUES(5,3)
INSERT INTO [PlantesOppIngredients] VALUES(13,4)
INSERT INTO [PlantesOppIngredients] VALUES(19,3)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eJm8%23xiaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> You need to include your DDL.
> http://www.aspfaq.com/etiquette.asp?id=5006
> Without knowing your keys we cannot determine why you are getting
> duplicates.
> My guess is that you are not joining on your full keys for at least one
> join.
> "alto" <altodorov@.hotmail.com> wrote in message
> news:%23ew9spiaGHA.4612@.TK2MSFTNGP03.phx.gbl...
> pOpp.ID
>sql

Friday, March 9, 2012

displaying found duplicate - both records

I would like to look at both records returned as duplicates. I am using the
following script to find the duplicates but can it only returns one record
not both. I would like to see the entire record so that I can make a
determination on more than just the criteria that I selected as the basis of
the record being a duplicate.
SELECT Patient.Last_Name,
Patient.First_Name,
Patient.Birthdate
COUNT(*) AS Dupes
FROM Patient
WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
Payor_Key = 36)
GROUP BY Last_Name, First_Name, Birthdate
HAVING (COUNT(*) > 1)
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1In order to do that, you may need to put your current query as "Result
Table", something like:
SELECT P1.Last_Name,
P1.First_Name,
P1.Birthdate,
Dupes
FROM Patient P1,
(SELECT P2.Last_Name P2LastName,
P2.First_Name P2FirstName,
P2.Birthdate P2Birthday
COUNT(*) AS Dupes
FROM Patient P2
WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FROM
Patient_Elg WHERE
P2.Payor_Key = 36)
GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
HAVING (COUNT(*) > 1)) DupCount
where P1.Last_Name = P2LastName
and P1.First_Name = P2FirstName
and P1.Birthdate = P2Birthday
This code has not been tested yet but just an idear.
Perayu
"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:5685c45d8a0c6@.uwe...
>I would like to look at both records returned as duplicates. I am using the
> following script to find the duplicates but can it only returns one record
> not both. I would like to see the entire record so that I can make a
> determination on more than just the criteria that I selected as the basis
> of
> the record being a duplicate.
> SELECT Patient.Last_Name,
> Patient.First_Name,
> Patient.Birthdate
> COUNT(*) AS Dupes
> FROM Patient
> WHERE Patient_Key IN (SELECT DISTINCT Patient_Key FROM Patient_Elg WHERE
> Payor_Key = 36)
> GROUP BY Last_Name, First_Name, Birthdate
> HAVING (COUNT(*) > 1)
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||This did work for me after I made some tweaks. Thanks allot.
Perayu wrote:
>In order to do that, you may need to put your current query as "Result
>Table", something like:
>SELECT P1.Last_Name,
> P1.First_Name,
> P1.Birthdate,
> Dupes
>FROM Patient P1,
> (SELECT P2.Last_Name P2LastName,
> P2.First_Name P2FirstName,
> P2.Birthdate P2Birthday
> COUNT(*) AS Dupes
> FROM Patient P2
> WHERE P2.Patient_Key IN (SELECT DISTINCT Patient_Key FRO
M
>Patient_Elg WHERE
> P2.Payor_Key = 36)
> GROUP BY P2.Last_Name, P2.First_Name, P2.Birthdate
> HAVING (COUNT(*) > 1)) DupCount
>where P1.Last_Name = P2LastName
> and P1.First_Name = P2FirstName
> and P1.Birthdate = P2Birthday
>This code has not been tested yet but just an idear.
>Perayu
>
>[quoted text clipped - 13 lines]
Message posted via http://www.webservertalk.com

Wednesday, March 7, 2012

displaying all records of duplicates

HI all I have this table below and i need to get records from col1 >1 and col2 >1

col1 col2 language
1 a s
1 b p
3 c e
3 c f

SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)

with the above query it gives me this

col1 col2 expr1
3 c 2

the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below

col1 col2 expr1 language
3 c 2 e
3 c 2 f

I need the result above inorder to normalize the table and bring out the language part into another table

thanksSELECT t.col1, t.col2, d.daCount, t.language
FROM table1 AS t
INNER
JOIN (
SELECT col1, col2, COUNT(*) AS daCount
FROM table1
GROUP BY col1, col2
HAVING COUNT(*) > 1
) as d
ON c.col1 = t.col1
AND d.col2 = t.col2|||thanks Rudy

Got it...

Tuesday, February 14, 2012

Display Duplicates Entry from Sql Server

Hello,

I like to ask if anyone knows the sql statement to display all the field of a record from a table only showing the duplicates, and my criteria of duplicate is that a record has two fields with the same data. Thanks in advance.

Regards,

stealthnet

This one would show the field, and count of occurrences when there are more than one occurrence of that field in the table.

SELECT field, count(*) FROM table
GROUP BY field
HAVING (count(*) > 1)

|||

Thanks!