selecting non distinct rows - 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

SQL Server 2000 Forums

SQL Server Development (2000)

selecting non distinct rows

Author

Topic

NewCents
Starting Member

19 Posts

Posted - 2006-06-26 : 21:22:33

i'm trying to develop a SQL query which will produce all the non distinct rows in a table. For example:

Original Table:
alpha_part_num manufacture_part_num
C26312-H125-B23 C26312-H125-B24
C26312-H125-B23 C26312-H125-B24
C26312-H125-B30 C26312-H125-B31
C26312-H125-B40 C26312-H125-B41

after query:
C26312-H125-B23 C26312-H125-B24
C26312-H125-B23 C26312-H125-B24

Thank you for any help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-26 : 21:30:04

[code]select t.*
from table t
inner join
(
select alpha_part_num, manufacture_part_num
from table
group by alpha_part_num, manufacture_part_num
having count(*) > 1
) d
on t.alpha_part_num = d.alpha_part_num
and t.manufacture_part_num = d.manufacture_part_num[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-27 : 01:46:06

Do you want to return no distinct rows?
your expected result shows you want distinct rows. Right?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 06:10:50

Hi all

Here's another option. I've put both side-by-side for comparison. (2) performs slightly better in this example, but you'll have to test for your particular situation (if speed is important).

--data
declare @t table (id int identity(1, 1), alpha_part_num varchar(20), manufacture_part_num varchar(20))
insert @t
          select 'C26312-H125-B23', 'C26312-H125-B24'
union all select 'C26312-H125-B23', 'C26312-H125-B24'
union all select 'C26312-H125-B30', 'C26312-H125-B31'
union all select 'C26312-H125-B40', 'C26312-H125-B41'

--calculation (1)
select	t.*
from	@t t
inner join
(
	select	alpha_part_num, manufacture_part_num
	from	@t
	group by alpha_part_num, manufacture_part_num
	having count(*) > 1
) d
on	t.alpha_part_num	= d.alpha_part_num
and	t.manufacture_part_num	= d.manufacture_part_num

--calculation (2)
select * from @t a where exists (
select alpha_part_num, manufacture_part_num
from @t
where alpha_part_num = a.alpha_part_num and manufacture_part_num = a.manufacture_part_num
group by alpha_part_num, manufacture_part_num
having count(*) > 1)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-27 : 09:28:15

Here is another crack at the issue. Works only if source table has unique key! But it is fast.

declare @test table (id int identity(0, 1), alpha_part_num varchar(50), manufacture_part_num varchar(50))

insert @test
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B30', 'C26312-H125-B31' UNION ALL
select 'C26312-H125-B40', 'C26312-H125-B41'


select		t1.*
from		@test t1
inner join	@test t2 on t2.id <> t1.id and t2.alpha_part_num = t1.alpha_part_num and t2.manufacture_part_num = t1.manufacture_part_num

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-27 : 10:18:16

Well, if you're going to be like that...

--data
declare @test table (id int identity(0, 1), alpha_part_num varchar(50), manufacture_part_num varchar(50))
insert @test
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B30', 'C26312-H125-B31' UNION ALL
select 'C26312-H125-B40', 'C26312-H125-B41'

--calculation (1)
select		t1.*
from		@test t1
inner join	@test t2 on t2.id <> t1.id and t2.alpha_part_num = t1.alpha_part_num and t2.manufacture_part_num = t1.manufacture_part_num

--calculation (2)
select		*
from		@test a
where exists (select * from @test where not id = a.id and alpha_part_num = a.alpha_part_num and manufacture_part_num = a.manufacture_part_num)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2006-06-28 : 06:17:24

madhivanan is right. if he wants non distinct records then...

select alpha_part_num, manufacture_part_num from <table_name>
group by alpha_part_num manufacture_part_num
having count(alpha_part_num) = 1

BMahesh

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-28 : 06:31:02

Well, you've confused me there mahesh_bote. That query seems, to me, to give distinct records (see below), not "non-distinct" records. The results below are different to the ("after query") results requested in the original post.

There must be some misunderstanding here.

--data
declare @test table (id int identity(0, 1), alpha_part_num varchar(50), manufacture_part_num varchar(50))
insert @test
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B23', 'C26312-H125-B24' UNION ALL
select 'C26312-H125-B30', 'C26312-H125-B31' UNION ALL
select 'C26312-H125-B40', 'C26312-H125-B41'

--calculation
select alpha_part_num, manufacture_part_num from @test
group by alpha_part_num, manufacture_part_num
having count(alpha_part_num) = 1

/*results
alpha_part_num                                     manufacture_part_num                               
-------------------------------------------------- -------------------------------------------------- 
C26312-H125-B30                                    C26312-H125-B31
C26312-H125-B40                                    C26312-H125-B41
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 07:08:01

>>There must be some misunderstanding here.

Thats why I asked question to the Questioner
Read my first reply

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-28 : 07:24:23

quote:
Originally posted by mahesh_bote

madhivanan is right. if he wants non distinct records then...

select alpha_part_num, manufacture_part_num from <table_name>
group by alpha_part_num manufacture_part_num
having count(alpha_part_num) = 1

BMahesh

This will only produce ONE copy of the duplicate rows, not all as the original poster stated.

Peter Larsson
Helsingborg, Sweden

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-28 : 07:28:59

quote:
Thats why I asked question to the Questioner
Read my first reply

I have. Several times. I couldn't understand why you asked the question because there seemed to be no ambiguity in the question.

Do you see ambiguity? If so, I want to understand where you see it...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-28 : 07:31:21

quote:
This will only produce ONE copy of the duplicate rows, not all as the original poster stated.

Not true, Peso. I think you misread the = as a > ...

But then, that's not the main point...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-06-28 : 07:44:56

I think what the NC wanted are those rows which have repeted combination of alpha_part_num and manufacture_part_num. For getting the result which u need, I think following simple query shud work:

select * from #temp a where not exists (Select alpha_part_num, manufacture_part_num from #temp b
where a.alpha_part_num = b.alpha_part_num
and a.manufacture_part_num = b.manufacture_part_num group by alpha_part_num, manufacture_part_num having count(*) = 1)

Harsh Athalye
India.
"Nothing is Impossible"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-28 : 08:20:24

NewCents - I make that 5 different methods for you to try!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-28 : 08:51:42

quote:
Originally posted by RyanRandall

quote:
Thats why I asked question to the Questioner
Read my first reply
I have. Several times. I couldn't understand why you asked the question because there seemed to be no ambiguity in the question.

Do you see ambiguity? If so, I want to understand where you see it...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

The fist line reads

quote:
i'm trying to develop a SQL query which will produce all the non distinct rows in a table

Lastly,

quote:
after query:
C26312-H125-B23 C26312-H125-B24
C26312-H125-B23 C26312-H125-B24

It is not clear whether the questioner wants Distinct or Non distinct rows

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-06-28 : 09:25:36

quote:

after query:
C26312-H125-B23 C26312-H125-B24
C26312-H125-B23 C26312-H125-B24

But these are the same row, so that makes them 'non-distinct' in my book. Is that not right?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-29 : 01:35:36

I want to wait until the questioner responds

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 03:59:24

Still waiting...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-09 : 04:09:44

Oh, so that's the way to boost your post-count?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-05-09 : 04:55:51

quote:
Originally posted by RyanRandall

Still waiting...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Yes I am still waiting

How did you find this thread?

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 08:41:19

quote:
How did you find this thread?

I was searching for this thread http://www.sqlteam.com/FORUMS/topic.asp?TOPIC_ID=101117 and came across this one due to my poor searching technique

Peso - How about that other thread?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources