select distinct with group by

Table1 lists items by serial number and stock number.

We need a  script that will select each serial number that is listed with more than one stock number.

sample data table1
serial number         stock number
  100                                 4
  100                                 5
  100                                 5
  100                                 9
   101                                4
   102                                 5
   103                                 4
   110                                  1
   110                                   1
   110                                   2  


from these I would need to select serial number 100 three times, once with stock number 4, once with stock number 5 and once with stock number 9
Would also need to capture serial number 110 once with stock number 1, and once with stock number 2.

All insights and suggestions appreciated.
J RAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
If you can use the following query it will do what you are looking for..

Select Distinct
serial number, stock number 
from table1

Open in new window


Saurabh...
J RAuthor Commented:
Thank you for your reply.

Your query will indeed capture the lines we need, but it will also capture many lines we do not need.  It will capture
101    4
102    5.    etc.  

we need to eliminate those rows that do not qualify as a serial number that is listed with more than one stock number.
J RAuthor Commented:
this is sample data, the actual table has millions of non-qualifying rows... we do not want them
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saurabh Singh TeotiaCommented:
You can use this with having statement to do what you are looking for...

Select Distinct
serial number, stock number 
from table1
having count(serial number)>1

Open in new window


Saurabh...
Saurabh Singh TeotiaCommented:
Also on second thoughts use it after a sub query because assuming same serial number and stock number exists multiple times..The sub query will take care of it..and then on top of it this will take care of the data you get...

select * from 
(Select Distinct
serial number, stock number 
from table1) tb1
having count(tb1.serial number)>1

Open in new window


Saurabh...
J RAuthor Commented:
That returned an error >> not a single group function <<
Saurabh Singh TeotiaCommented:
Yeah my bad you need to put group by..so you can use this...

select * from 
(Select Distinct
serial number, stock number 
from table1) tb1
group by tb1.serial number,tb1.stock number
having count(tb1.serial number)>1

Open in new window

J RAuthor Commented:
still getting not a single-group group function...
Saurabh Singh TeotiaCommented:
You should not get error on this..

select tb1.serial number,tb1.stock number from 
(Select Distinct
serial number, stock number 
from table1) tb1
group by tb1.serial number,tb1.stock number
having count(tb1.serial number)>1

Open in new window

slightwv (䄆 Netminder) Commented:
I'm thinking there has to be an easier way but I believe this will get you what you want:
select distinct serial_number, stock_number from
tab1 where serial_number
in(
	select serial_number from 
	(
		select serial_number,
			row_number() over(partition by serial_number, stock_number order by serial_number, stock_number) rn
		from tab1
	)
	where rn>1
)
order by 1,2
/

Open in new window

J RAuthor Commented:
this is now returning data, and it is listing serials that have more than one listing, but it is only listing ONLY one line for each serial.  It should list a line for each serial/stock number combination where there is more than one serial/stock number combination..

Thank you for your help, feel like we are close to the correct answer...
J RAuthor Commented:
my last reply was to Saurabh Singh Teotia2015-03-13 at 14:26:12ID: 40664307..


Thank you slightwv I will try your solution,.,..
Saurabh Singh TeotiaCommented:
Another efficient way of doing this...

select tb1.serial number,tb1. stock number
from table tb1
group by tb1.serial number, tb1. stock number
having count( distinct tb1.serial number || tb1.stock number)>1

Open in new window


And the original query will become...

select tb1.serial number,tb1.stock number from 
(Select Distinct
serial number, stock number 
from table1) tb1
group by tb1.serial number,tb1.stock number
having count( distinct tb1.serial number || tb1.stock number)>1

Open in new window


Saurabh...
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
slightwv's SQL should get you the expected results.
This is another way:
select distinct serial_number, stock_number from
table1
where serial_number in (
  select serial_number from table1
  group by serial_number
  having count(distinct stock_number) > 1
)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J RAuthor Commented:
Thank you slightwv!!!

Your solution returns the needed data... it also seems to return data when the serial/stock number has more than one line for one distinct listing..  We can live with that....

You are right in that this seems like it should be rather simple... but when I try to apply my thinking, over and over I either got nothing or way too much data....

I am going to count your solution as the solution and move forward..  If the simpler solution occurs to you, please feel free to add it.  

Thanks a million!!


And Saurabh Singh Teotia, thank you for your responses as well.  I do feel you would have eventually arrived at maybe even a more elegant solution, but this one works!!


I am impressed with E-E to date!!
Saurabh Singh TeotiaCommented:
Jr..

Not for points..can you try the solution which i gave to you the first one and let me know if that gave you the same records what you are looking for..

Saurabh...
J RAuthor Commented:
Thank you all very much.  Very impressive group work here..  I will try all solutions and may re-grade.... Don't know how important it is, but will try to arrive at best..  I have to transfer proposed solution to actual table that does not look a lot like the sample data, but it means the same as far as solutions go.
slightwv (䄆 Netminder) Commented:
>>can you try the solution which i gave to you the first one and let me know

I tried them all.  None of them provided results and  require editing since Oracle columns need double quotes with spaces in the column names.

I will post a complete test case below.  I suggest you test the code before posting.

I know my code works because I tested it.  I bet Qlemo did as well.

>>I  am going to count your solution as the solution and move forward.

Also check the one by Qlemo.

One of the two will be more efficient given larger amounts of data and indexing.  With a small sample it is impossible to tell.

Test case setup:
create table tab1(serial_number number,stock_number number);
insert into tab1 values(100,4);
insert into tab1 values(100,5);
insert into tab1 values(100,5);
insert into tab1 values(100,9);
insert into tab1 values(101,4);
insert into tab1 values(102,5);
insert into tab1 values(103,4);
insert into tab1 values(110,1);
insert into tab1 values(110,1);
insert into tab1 values(110,2);
commit;

Open in new window

slightwv (䄆 Netminder) Commented:
>>Don't know how important it is, but will try to arrive at best.

Accepting the correct solution or solutions is more important than the point splits.

Other members search the knowledge base for solutions to their questions.  They should be able to scroll down to the accepted/assisted answers and get what they need.
J RAuthor Commented:
Saurabh Singh Teotia, I have tried your script and so far it is not returning any data.  I will look it over closely as I transfer my actual table and columns into it..
slightwv (䄆 Netminder) Commented:
That is what I mean about accepting the correct answers.  I understand wanting to give everyone points but...

Using my setup and the code in http:#a40664330 I get no rows.  Did you get data using that SQL?
J RAuthor Commented:
Qlemo,

Your solution provided the quickest return of data, and also provided ONLY the needed data.  

If I can figure out how to re-order the points, I will do so..

Your solution is BEST!!!!


Thank you..

Thank you all!!!!

This is really something... to get this sort of electric group thinking and work for one lowly little request..
Saurabh Singh TeotiaCommented:
I don't have a system where sql is their..so not able to test the query myself before posting... Its either of the 3 signs which combine 2 columns in sql which || or + or comma..

Can you try this one...

select tb1.serial number,tb1. stock number
from table tb1
group by tb1.serial number, tb1. stock number
having count( distinct tb1.serial number,tb1.stock number)>1

Open in new window


And..

select tb1.serial number,tb1.stock number from 
(Select Distinct
serial number, stock number 
from table1) tb1
group by tb1.serial number,tb1.stock number
having count( distinct tb1.serial number,tb1.stock number)>1

Open in new window


If that doesn't work change comma with + sign that should do it for you for sure..

Saurabh...
Saurabh Singh TeotiaCommented:
And just open the question again by clicking on request to attention and moderator will help you to open the question and re-assign the points as you like...
slightwv (䄆 Netminder) Commented:
>>I don't have a system where sql is their..so not able to test the query myself before

sqlfiddle?  It will do Oracle.

>>Your solution provided the quickest return of data,

Make sure you compare apples to apples.  Running a select can buffer all the blocks. This can make subsequent selects appear faster when they may not be.

>>and also provided ONLY the needed data.

Curious:  What did mine return that wasn't necessary?
slightwv (䄆 Netminder) Commented:
I think I found a potential issue with Qlemo's.

Given the requirements, what if I add:
insert into tab1 values(102,6);

I read that you would not want 102 returned since the stock number doesn't repeat.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Steve,

That is what I expect. Each stock number should appear once, as soon as a serial number has more than one unique stock number.
Saurabh Singh TeotiaCommented:
Qlemo...

One potential bug i found as quoted by his example as well is stock number which is repeated..so lets say if the stock number is repeated for 2 serial numbers..for instance like..

100        5
100        6
101        5
101        6

It will show only 1 entry of them rather showing both of it..Again i can't verify it fully right now but based on the query since you are picking distinct stock number and in this case it's not distinct it wont pick both..However if the same data is..

100         5
100        6
101        5
101        8

Then it will pick it up that entry due to 8 and again assuming 8 is not their in any of the stock number..so that's why i feel you need to combine both stock number and serial number to see the distinct values...

Saurabh...
slightwv (䄆 Netminder) Commented:
>>That is what I expect.

I think I see now.  I might have read more into the sample data that what is expected.

I took it as:  Get the serial numbers that have repeating stock numbers and give me the distinct combinations.

Now that I re-read it, you might be correct!

Given my new found insight, this only hits the table once:
select serial_number, stock_number from (
	select serial_number, stock_number,
		row_number() over(partition by serial_number, stock_number order by serial_number, stock_number) rn,
		count(*) over(partition by serial_number order by serial_number) cnt
	from tab1
)
where cnt > 1 and rn=1
/

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The "distinct" refers to the combination of serial number and stock number already. It is not applied to a single column. Both of your examples will work, and result in 4 rows.
slightwv (䄆 Netminder) Commented:
>>Again i can't verify it fully right now

Take my test case and paste in into sqlfiddle.
Saurabh Singh TeotiaCommented:
So Qlemo if i understand correctly even if i apply..lets says

select distinct stock number
from table1

it will list down lets say stock number "5" lets say 5 times because it has 5 different serial number assigned to it which is lets say 100,101,102,103 and 104 ?
J RAuthor Commented:
slightwv2015-03-13 at 15:46:11ID: 40664457

To be sure I am referring all to the appropriate post/script, this script returns excessive data.  it is returning rows where the serial/stock are duplicated, not exclusively when the serial/ is listed with multiple stock numbers, which is that which we seek here.

-*/-*/-*-*/-*/

slightwv2015-03-13 at 14:31:09ID: 40664317
 this returned the desired results.

Qlemo2015-03-13 at 14:41:23ID: 40664335
this returned the desired results.

Sans objections, I will mark these two answers as acceptable solutions.

Thank you everyone.
.
slightwv (䄆 Netminder) Commented:
>>not exclusively when the serial/ is listed with multiple stock numbers, which is that which we seek here.

Based on what Qlemo posted, I might have misunderstood the actual requirements based on the limited sample data you posted.

Please compare what I posted in http:#a40664457 with what Qlemo  posted.

Based on the sample data originally posted and the extra row I added, they return the same results and might perform better.

If it doesn't return the correct data, please add to the sample data and expected results.
J RAuthor Commented:
slightwv2015-03-13 at 17:35:52ID: 40664572
 you are spot on.  When I exchanged the sample table for reality, your script returned data not desired.  When run against the sample data, it returns exactly what it should.  SO the difference must be between my reality and my sample.

My apologies.  

... so

Sans objections, I will add the script in slightwv2015-03-13 at 17:35:52ID: 40664572
 to acceptable solutions.


Thank you !!

I will wait a bit before marking anything again to be sure I don't offend any parties.

Thank you all once more!!
Saurabh Singh TeotiaCommented:
jr..can you let me know does my suggestion gave you desired outcome or not??
slightwv (䄆 Netminder) Commented:
>>I will wait a bit before marking anything again to be sure I don't offend any parties.

Our job is to make sure you get the answer to your questions.  Accept those that helped.  Ignore the ones that don't.  Sure, some times the Experts get offended but we get over it and when we don't, we have Moderators and Admins!  ;)

>> SO the difference must be between my reality and my sample.

Feel free to add data to the samples and expected results as you fine-tune the question.  Many times, what you think is good sample data needs to be adjusted to adjust for unforeseen real world environments.

>>you let me know does my suggestion gave you desired outcome or not??

Again:  sqlfiddle...

Given the test case and added row, it appears the expected results are:
SERIAL_NUMBER STOCK_NUMBER
------------- ------------
          100            4
          100            5
          100            9
          102            5
          102            6
          110            1
          110            2

Open in new window


You have a complete test case and expected results.  sqlfiddle will set it all up for you.

Test what you have posted and see if you get the above results.

Here, let me help:
http://sqlfiddle.com/#!4/87a15/2

For EE:
drop table tab1 purge;
create table tab1(serial_number number,stock_number number);
insert into tab1 values(100,4);
insert into tab1 values(100,5);
insert into tab1 values(100,5);
insert into tab1 values(100,9);
insert into tab1 values(101,4);
insert into tab1 values(102,5);
insert into tab1 values(102,6);
insert into tab1 values(103,4);
insert into tab1 values(110,1);
insert into tab1 values(110,1);
insert into tab1 values(110,2);
commit;

select distinct serial_number, stock_number from
tab1
where serial_number in (
  select serial_number from tab1
  group by serial_number
  having count(distinct stock_number) > 1
)
order by 1,2
/


select serial_number, stock_number from (
	select serial_number, stock_number,
		row_number() over(partition by serial_number, stock_number order by serial_number, stock_number) rn,
		count(*) over(partition by serial_number order by serial_number) cnt
	from tab1
)
where cnt > 1 and rn=1
order by 1,2
/

Open in new window

J RAuthor Commented:
Saurabh Singh Teotia2015-03-13 at 18:30:31ID: 40664606

"jr..can you let me know does my suggestion gave you desired outcome or not?? "

No. Neither script returns data.  ORA-00909 invalid number of arguments following "having count"

Don't know about adding a + sign, this is for Oracle SQL...

But thank you for your efforts... Maybe you can use http://sqlfiddle.com/#!4/87a15/2 to work through the errors.

It helped me see the eventual Solutions..
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.