Duplicate values - SQL Server Forums

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

Duplicate values

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-05-11 : 06:26:47

Hi,

I have the following SQL select statement which gives results as;


SELECT     TOP (100) PERCENT gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media, MAX(gprdsql.TblCollections.col_date) AS MaxColDate, 
                      MAX(gprdsql.TblCollections.audit_end+1) AS audit_start
FROM         gprdsql.TblCollections INNER JOIN
                      gprdsql.TblPracDetails ON gprdsql.TblCollections.prac_no = gprdsql.TblPracDetails.prac_no INNER JOIN
                      dbo.QryMaxColDate ON gprdsql.TblCollections.prac_no = dbo.QryMaxColDate.prac_no AND 
                      gprdsql.TblCollections.col_date = dbo.QryMaxColDate.col_date
WHERE     (gprdsql.TblPracDetails.prac_status = 'Active') AND (gprdsql.TblCollections.stage = 'Processed')
GROUP BY gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media, gprdsql.TblCollections.media_type, gprdsql.TblPracDetails.prac_status
ORDER BY gprdsql.TblPracDetails.prac_no

prac_no submedia MaxColdate audit_start

1 Email 01/03/2012 56
2 Email 17/04/2012 89
3 Email 21/04/2012 73
3 Email 21/04/2012 70
4 Email 05/05/2012 8282

The problem with prac_no 3 - it has 2 MaxColdate with different audit_end values. Therefore, I want to take the maximum out of the two and ignore the other one.. Therefore, in this case the result should be;

prac_no submedia MaxColdate audit_start

1 Email 01/03/2012 56
2 Email 17/04/2012 89
3 Email 21/04/2012 73
4 Email 05/05/2012 8282

Thanks

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-11 : 06:44:57

[code] Try This.

SELECT T1.prac_no, T1.submedia T1.MaxColdate ,MAX( T1.audit_start) audit_start FROM (

SELECT TOP (100) PERCENT gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media, MAX(gprdsql.TblCollections.col_date) AS MaxColDate,
MAX(gprdsql.TblCollections.audit_end+1) AS audit_start
FROM gprdsql.TblCollections INNER JOIN
gprdsql.TblPracDetails ON gprdsql.TblCollections.prac_no = gprdsql.TblPracDetails.prac_no INNER JOIN
dbo.QryMaxColDate ON gprdsql.TblCollections.prac_no = dbo.QryMaxColDate.prac_no AND
gprdsql.TblCollections.col_date = dbo.QryMaxColDate.col_date
WHERE (gprdsql.TblPracDetails.prac_status = 'Active') AND (gprdsql.TblCollections.stage = 'Processed')
GROUP BY gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media, gprdsql.TblCollections.media_type, gprdsql.TblPracDetails.prac_status
ORDER BY gprdsql.TblPracDetails.prac_no
) T1 GROUP BY T1.prac_no, T1.submedia T1.MaxColdate

[/code]

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2012-05-11 : 06:49:39

Cant I update the query to perform the task - few applications are dependent on this query and dont want to create another table then run another query.
Is there any chance I can do to have the desired within the same query?

Thank you

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-11 : 07:02:24

[code]
SELECT TOP (100) PERCENT gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media, MAX(gprdsql.TblCollections.col_date) AS MaxColDate,
MAX(gprdsql.TblCollections.audit_end+1) AS audit_start
FROM gprdsql.TblCollections INNER JOIN
gprdsql.TblPracDetails ON gprdsql.TblCollections.prac_no = gprdsql.TblPracDetails.prac_no INNER JOIN
dbo.QryMaxColDate ON gprdsql.TblCollections.prac_no = dbo.QryMaxColDate.prac_no AND
gprdsql.TblCollections.col_date = dbo.QryMaxColDate.col_date
WHERE (gprdsql.TblPracDetails.prac_status = 'Active') AND (gprdsql.TblCollections.stage = 'Processed')
GROUP BY gprdsql.TblPracDetails.prac_no, gprdsql.TblPracDetails.sub_media,MAX(gprdsql.TblCollections.col_date)
ORDER BY gprdsql.TblPracDetails.prac_no

If you remove below columns from your query from group by clause and add column which i have added in your query.This is just hit and trial . I hope this shoul work for you
gprdsql.TblCollections.media_type, gprdsql.TblPracDetails.prac_status
[/code]

markandan26
Starting Member

3 Posts

Posted - 2012-07-23 : 00:57:39

This is my query.

update two column in same table and update duplicate values in new column.
I'm having query for this update but it will work for single column. i want two column
please help me........

UPDATE [Table] SET [Table].DUP = "Duplicate"
WHERE ((([Table].column1) In (SELECT [column1] FROM [Table] As Tmp WHERE TMP.ID <= [Table].ID GROUP BY [column1] HAVING Count(*)>1 )));

Thank U..................

Markandan M

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-23 : 10:32:49

quote:
Originally posted by markandan26

This is my query.

update two column in same table and update duplicate values in new column.
I'm having query for this update but it will work for single column. i want two column
please help me........

UPDATE [Table] SET [Table].DUP = "Duplicate"
WHERE ((([Table].column1) In (SELECT [column1] FROM [Table] As Tmp WHERE TMP.ID <= [Table].ID GROUP BY [column1] HAVING Count(*)>1 )));

Thank U..................

Markandan M

use a join then


UPDATE t  
SET t.DUP = 'Duplicate'
FROM [Table] t
INNER JOIN(SELECT [column1],[column2] 
FROM [Table] 
GROUP BY [column1],[column2] 
HAVING Count(*)>1 )t1
ON t1.column1 = t. column1 
AND t1.column2 = t.column2

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources