removing Duplicate in query - 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

removing Duplicate in query

Author

Topic

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 07:04:18

Hi Guys

I have had a data request for certain data to be extracted, when I extract this duplicates are appearing although it doesn’t look like a duplicate due to an automatic rowid being generated with every row but if you remove that it is a duplicate example below

row id co1 coll2 col3
1 1 1 1
2 1 1 1
3 1 2 4
4 5 7 9

in the above although it doesn’t look like there is duplicates however if you remove the row id the top two are duplicates is there way I can do a query where i can get the below from the top table

row id co1 coll2 col3
1 1 1 1
3 1 2 4
4 5 7 9

This is just a example I have loads of fields and if i was to remove the row id and another field next to it the data is duplicate ( i can see this due to the business)

If anyone can help please reply

thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-10 : 07:10:22

[code]SELECT
MIN(row_id) as RowID,
Col1,
Coll2,
Col3
FROM
YourTable
GROUP BY
Col1,
Coll2,
Col3;[/code]

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 07:26:37

when i tried that on my table it removed some not all take the below example

RowId Age d1 d3 e a b c month code nameee acode
1 19 01/01/2012 01/01/2012 1 1 wer e453 jan j
2 20 01/01/2012 01/01/2012 1 1 wer e453 jan j

imagine howing 1000s of fileds as the above, you can see its a duplicate take the row id and age out its the same want to do a query that will bring back the below

1 19 01/01/2012 01/01/2012 1 1 wer e453 jan j

basically a distinct row !!

thank you sunitabeck

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-10 : 07:42:09

You have a few choices, but each of them require you to list the columns. That is not as hard as it sounds - in object explorer, you can right click on the table and select Script Table as -> Select To to generate a select script.

SELECT
	MIN(RowID) AS RowId,
	MIN(Age) AS Age,
	d1,
	d3,ea,a,b,c,
	[month],code, nameee, acode
FROM
	YourTable
GROUP BY
	d1,
	d3,ea,a,b,c,
	[month],code, nameee, acode

If you don't want the age and rowId columns you can do a select distinct, but exclude those columns. Another way would be to use the row_number function - here is an example where I am trying to remove dups in Id3 and Id4.

CREATE TABLE #tmp (id1 INT, id2 INT, id3 INT, id4 INT);
INSERT INTO #tmp VALUES (1,1,2,22),(1,2,2,22),(3,3,2,22),(4,4,7,8),(4,5,7,8);

SELECT * FROM
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY id3,id4 ORDER BY (SELECT NULL)) RN
	FROM
		#tmp
) S 
WHERE RN=1;

DROP TABLE #tmp;

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 07:46:03

thank you i will try this and get back to you thank you so much

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 11:14:56

THAT WORKS thank you sunita ,

can you please explain what thats does?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 11:26:35

Sunita has used row number function to generate unique sequence numbers within repeated groups and is returned one out of them using condition WHERE RN=1;

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

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-10 : 17:15:02

visakh i was refering to SELECT
MIN(RowID) AS RowId,
MIN(Age) AS Age,
d1,
d3,ea,a,b,c,
[month],code, nameee, acode
FROM
YourTable
GROUP BY
d1,
d3,ea,a,b,c,
[month],code, nameee, acode

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 01:22:16

in the above suggestion, she's grouping by d1,
d3,ea,a,b,c,
[month],code, nameee, acode
combination so that each combination appears only once and then appyling MIN to ger minimum rowid and age within each group

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

w1102157
Yak Posting Veteran

80 Posts

Posted - 2012-01-11 : 07:33:03

Thank you Visakh

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-11 : 09:35:29

Can we really call this SQL anymore???

>> ROW_NUMBER() OVER (PARTITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 11:12:24

quote:
Originally posted by X002548

Can we really call this SQL anymore???

>> ROW_NUMBER() OVER (PARTITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

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

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

why?
is it because its not as per ANSI standards?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources