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
 Avoid duplicate values

Author  Topic 

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 13:36:13
Hi

I have a stored procedure which selects the unique jobname values from a table ,

below is my store procedure

SELECT t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.Jobname = t.Jobname
WHERE j.Jobname IS NULL


I just observed that this is selecting the duplicate jobnames , with different cases, i mean if there is a job in upper case i get the same job in lower case also ...

Could anybody please suggest me how to i avoid this , i need only unique jobs

Here is the example of the jobnames :Here both are same jobs but just the case is different ...

M7630AABBQOAAM12091000_bringup_17MAY2010_050738pm
M7630AABBQOAAM12091000_BRINGUP_17MAY2010_050738pm

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2010-05-18 : 13:44:30
You could change the collation to one that's case insensitive or use the UPPER or LOWER function on the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 13:47:20
could you please tell me what is collation?

i actually have a jobid associated with each job, even if i change the syntax of the stored procedure to

SELECT t.*
FROM BatchRecords_TEMP t
LEFT outer JOIN BatchRecords j ON j.JobName = t.JobName
WHERE j.JobID is null

i am getting the data with duplicate jobid's

I cannot understand why this is happening because i have almost 10-12 stored procedures with same syntax and no procedure allows duplicate values ...except this one ....


quote:
Originally posted by tkizer

You could change the collation to one that's case insensitive or use the UPPER or LOWER function on the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 13:52:17
see

http://msdn.microsoft.com/en-us/library/ms184391.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 14:03:56
quote:
Originally posted by rds207

could you please tell me what is collation?

i actually have a jobid associated with each job, even if i change the syntax of the stored procedure to

SELECT t.*
FROM BatchRecords_TEMP t
LEFT outer JOIN BatchRecords j ON j.JobName = t.JobName
WHERE j.JobID is null

i am getting the data with duplicate jobid's

I cannot understand why this is happening because i have almost 10-12 stored procedures with same syntax and no procedure allows duplicate values ...except this one ....


quote:
Originally posted by tkizer

You could change the collation to one that's case insensitive or use the UPPER or LOWER function on the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog


are all the other stored procedure looking for same column? if not, are the other columns having same collation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 14:14:45
No its a different column but similar to jobid , every table has an id on which i am having the above stored procedure , like where jobid is null and it gives me all the new jobid's...from temp(batchrecords_temp)table which are not there in main table (batchrecords), but only this stored procedure is giving me data with duplicate jobid's


quote:
Originally posted by visakh16

quote:
Originally posted by rds207

could you please tell me what is collation?

i actually have a jobid associated with each job, even if i change the syntax of the stored procedure to

SELECT t.*
FROM BatchRecords_TEMP t
LEFT outer JOIN BatchRecords j ON j.JobName = t.JobName
WHERE j.JobID is null

i am getting the data with duplicate jobid's

I cannot understand why this is happening because i have almost 10-12 stored procedures with same syntax and no procedure allows duplicate values ...except this one ....


quote:
Originally posted by tkizer

You could change the collation to one that's case insensitive or use the UPPER or LOWER function on the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog


are all the other stored procedure looking for same column? if not, are the other columns having same collation?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 14:20:05
may be this is column where you've same data existing in different case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 14:24:08
Jobid looks something like : 1746858

Thats the reason i have changed the syntax to where jobid is null, that mean it should give only unique jobid right?

quote:
Originally posted by visakh16

may be this is column where you've same data existing in different case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 14:25:25
nope. how will it give unique jobid just when you filter NULL values alone. it depends on whether duplicate exist in it. if exists, you need to use distinct or group by

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 14:29:02
Yes duplicates exists as well as NULLS also ...

could you please let me know how do i change the syntax to handle this?


quote:
Originally posted by visakh16

nope. how will it give unique jobid just when you filter NULL values alone. it depends on whether duplicate exist in it. if exists, you need to use distinct or group by

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-18 : 14:34:17
use DISTINCt or GROUP BY

like

SELECT jobID
FROM Table
GROUP BY JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 14:47:24
i changed to below,

SELECT distinct t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null and j.JobName is null
group by t.BatchRecordID,t.ApplicationName,t.Archived,t.BatchENV,t.JobName,
t.BatchQueueName,t.UserName,t.Site,t.SubmitTime,t.SubmitTimeSecs,t.StartTime,
t.StartTimeSecs,t.EndTime,t.EndTimeSecs,t.GMTDiffSecs,t.BatchServerName,t.JobCommands


receiving error :

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.




quote:
Originally posted by visakh16

use DISTINCt or GROUP BY

like

SELECT jobID
FROM Table
GROUP BY JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 15:19:52
SELECT distinct t.JobName
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null and j.JobID is null

above gives me single jobname, but i need to get all columns in select statement ..

Please Help


quote:
Originally posted by rds207

i changed to below,

SELECT distinct t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null and j.JobName is null
group by t.BatchRecordID,t.ApplicationName,t.Archived,t.BatchENV,t.JobName,
t.BatchQueueName,t.UserName,t.Site,t.SubmitTime,t.SubmitTimeSecs,t.StartTime,
t.StartTimeSecs,t.EndTime,t.EndTimeSecs,t.GMTDiffSecs,t.BatchServerName,t.JobCommands


receiving error :

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.




quote:
Originally posted by visakh16

use DISTINCt or GROUP BY

like

SELECT jobID
FROM Table
GROUP BY JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-18 : 20:01:40
Could body please assit me with this query ...i am trying to select the records which are not there in batchrecords but which exists in batchrecords_temp ...

Here i have duplicate jobnames with unique batchrecordid , so i used
over partition by clause to select unique jobnames ordered by batchrecordid , so where ever rownum =1 and if that particular job with rownum =1 is not there is batchrecords then i want that record

i tried below query , but seems to not work ....

select * from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
where x.Rownum = 1 and j.JobID is null


---------------------------------------
(select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)

--------------------------------------------

The above part works fine , but not sure how to select new jobs from batchrecords_temp where rownum =1 and which does not exits in batchrecords table.....

Please Help
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2010-05-19 : 10:05:44
Please post DDL, DML and Expected output. This link will help you to provide that information so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-19 : 12:31:33
Here is the DDL of 2 tables :

CREATE TABLE [dbo].[BatchRecords](
[BatchRecordID] [int] NOT NULL,
[JobName] [varchar](160) NOT NULL,
[ApplicationName] [varchar](50) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Site] [varchar](50) NOT NULL,
[SubmitTime] [datetime] NOT NULL,
[SubmitTimeSecs] [int] NOT NULL,
[StartTime] [datetime] NULL,
[StartTimeSecs] [int] NULL,
[EndTime] [datetime] NULL,
[EndTimeSecs] [int] NULL,
[GMTDiffSecs] [int] NOT NULL,
[BatchServerName] [varchar](50) NOT NULL,
[BatchQueueName] [varchar](50) NOT NULL,
[JobCommands] [text] NOT NULL,
[JobPath] [varchar](255) NOT NULL,
[JobLimit] [int] NOT NULL,
[JobCount] [int] NOT NULL,
[RunSite] [varchar](50) NULL,
[RunGMTDiffSecs] [int] NULL,
[HoldFlag] [bit] NOT NULL,
[DayLightFlag] [bit] NOT NULL,
[BatchStatus] [varchar](15) NULL,
[Archived] [bit] NULL,
[ArchiveDate] [datetime] NULL,
[BatchENV] [varchar](50) NULL,
[JobID] [varchar](50) NULL,
[JobStatus] [varchar](50) NULL,
[PoolName] [varchar](50) NULL,
[PoolSize] [varchar](50) NULL,
[PoolUsed] [varchar](50) NULL,
[TerminationUser] [varchar](50) NULL,
[TerminationHost] [varchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Table 2:

CREATE TABLE [dbo].[BatchRecords_TEMP](
[BatchRecordID] [int] NOT NULL,
[JobName] [varchar](160) NOT NULL,
[ApplicationName] [varchar](50) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Site] [varchar](50) NOT NULL,
[SubmitTime] [datetime] NOT NULL,
[SubmitTimeSecs] [int] NOT NULL,
[StartTime] [datetime] NULL,
[StartTimeSecs] [int] NULL,
[EndTime] [datetime] NULL,
[EndTimeSecs] [int] NULL,
[GMTDiffSecs] [int] NOT NULL,
[BatchServerName] [varchar](50) NOT NULL,
[BatchQueueName] [varchar](50) NOT NULL,
[JobCommands] [text] NOT NULL,
[JobPath] [varchar](255) NOT NULL,
[JobLimit] [int] NOT NULL,
[JobCount] [int] NOT NULL,
[RunSite] [varchar](50) NULL,
[RunGMTDiffSecs] [int] NULL,
[HoldFlag] [bit] NOT NULL,
[DayLightFlag] [bit] NOT NULL,
[BatchStatus] [varchar](15) NULL,
[Archived] [bit] NULL,
[ArchiveDate] [datetime] NULL,
[BatchENV] [varchar](50) NULL,
[JobID] [varchar](50) NULL,
[JobStatus] [varchar](50) NULL,
[PoolName] [varchar](50) NULL,
[PoolSize] [varchar](50) NULL,
[PoolUsed] [varchar](50) NULL,
[TerminationUser] [varchar](50) NULL,
[TerminationHost] [varchar](50) NULL,
[RowNum] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


The Stored Procedure i have been using to insert data into bathcrecords from batchrecords_temp , but this is giving me duplicate jobnames, as batchrecordid is a unique row and each jobname has a unique batchrecordid...

What i need is unique jobnames .........

Expected Results :

If the data in batchrecords_temp is ,

JOBNAME BATCHRECORDID
M7630AABBQOAAM12091000_bringup_17MAY2010_050738pm 452369
M7630AABBQOAAM12091000_BRINGUP_17MAY2010_050738pm 452370

Expected output is (not really specific about the case , i just need one jobname)

M7630AABBQOAAM12091000_bringup_17MAY2010_050738pm 452369

What i have tried :This statement assignes a rownum to the jobs and i want to select the jobname with rownum as 1 , But the below statement does not work,


select * from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
where x.Rownum = 1 and j.JobID is null


This statement(select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP), works fine for assigning rownum but i cannot understand how to select the all columns from temp table with one jobname



SELECT t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null


quote:
Originally posted by Lamprey

Please post DDL, DML and Expected output. This link will help you to provide that information so we can help you better:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-19 : 12:41:30
quote:
Originally posted by rds207

i changed to below,

SELECT distinct t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null and j.JobName is null
group by t.BatchRecordID,t.ApplicationName,t.Archived,t.BatchENV,t.JobName,
t.BatchQueueName,t.UserName,t.Site,t.SubmitTime,t.SubmitTimeSecs,t.StartTime,
t.StartTimeSecs,t.EndTime,t.EndTimeSecs,t.GMTDiffSecs,t.BatchServerName,t.JobCommands


receiving error :

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.




quote:
Originally posted by visakh16

use DISTINCt or GROUP BY

like

SELECT jobID
FROM Table
GROUP BY JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




you cant take DISTINCT on above fields. for taking DISTINCT try casting them to varchar(max),nvarchar(max),..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-19 : 12:43:36

Hey ,

I just tried the below statement , which gives me unique jobnames ,
Now how do i select new jobnames which are not there in batchrecords and there in batchrecords_temp?

;with cte as
(SELECT BatchRecordID,JobName,ApplicationName,USERNAME,site,submittime,SubmitTimeSecs
starttime,StartTimeSecs,EndTime,EndTimeSecs,GMTDiffSecs,BatchServerName,BatchQueueName,JobCommands,JobPath,JobLimit,
jobcount,runsite,rungmtdiffsecs,holdflag,daylightflag,batchstatus,archived,archivedate,
batchenv,jobid,jobstatus,poolname,poolsize,poolused,terminationuser,TerminationHost, row_number() over( PARTITION BY JOBNAME ORDER BY batchrecordid) as RowNumByJobName
FROM BatchRecords_TEMP)select * from cte where RowNumByJobName = 1


quote:
Originally posted by visakh16

quote:
Originally posted by rds207

i changed to below,

SELECT distinct t.*
FROM BatchRecords_TEMP t
LEFT JOIN BatchRecords j ON j.BatchRecordID = t.BatchRecordID
WHERE j.BatchRecordID is null and j.JobName is null
group by t.BatchRecordID,t.ApplicationName,t.Archived,t.BatchENV,t.JobName,
t.BatchQueueName,t.UserName,t.Site,t.SubmitTime,t.SubmitTimeSecs,t.StartTime,
t.StartTimeSecs,t.EndTime,t.EndTimeSecs,t.GMTDiffSecs,t.BatchServerName,t.JobCommands


receiving error :

Msg 306, Level 16, State 2, Line 7
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Msg 421, Level 16, State 1, Line 1
The text data type cannot be selected as DISTINCT because it is not comparable.




quote:
Originally posted by visakh16

use DISTINCt or GROUP BY

like

SELECT jobID
FROM Table
GROUP BY JobID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




you cant take DISTINCT on above fields. for taking DISTINCT try casting them to varchar(max),nvarchar(max),..

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-19 : 12:44:43
quote:
Originally posted by rds207
select * from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
where x.Rownum = 1 and j.JobID is null

This statement(select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP), works fine for assigning rownum but i cannot understand how to select the all columns from temp table with one jobname





the query looks fine. why it didnt work for you? what was the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

rds207
Posting Yak Master

198 Posts
Posted - 2010-05-19 : 12:53:47

I am not getting any error ,

i am getting the data where the jobid is null , that means i have data where jobid is NULL , what i was expecting was new jobid 's


quote:
Originally posted by visakh16

quote:
Originally posted by rds207
select * from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
where x.Rownum = 1 and j.JobID is null

This statement(select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP), works fine for assigning rownum but i cannot understand how to select the all columns from temp table with one jobname





the query looks fine. why it didnt work for you? what was the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-05-19 : 13:00:21
quote:
Originally posted by rds207


I am not getting any error ,

i am getting the data where the jobid is null , that means i have data where jobid is NULL , what i was expecting was new jobid 's


quote:
Originally posted by visakh16

quote:
Originally posted by rds207
select * from (select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP)x
left join BatchRecords j ON j.BatchRecordID = x.BatchRecordID
where x.Rownum = 1 and j.JobID is null

This statement(select *, row_number() over (partition by JobName order by batchrecordid) as Rownum from BatchRecords_TEMP), works fine for assigning rownum but i cannot understand how to select the all columns from temp table with one jobname





the query looks fine. why it didnt work for you? what was the error?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




but you're checking JobID iS NULL in where filter. then you will definitely get the ones with no match in BatchRecords table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site