select distinct with group by

If you can use the following query it will do what you are looking for..

Select Distinct
serial number, stock number 
from table1

Select allOpen in new window

Saurabh...

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.

this is sample data, the actual table has millions of non-qualifying rows... we do not want them

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

Select allOpen in new window

Saurabh...

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

Select allOpen in new window

Saurabh...

That returned an error >> not a single group function <<

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

Select allOpen in new window

still getting not a single-group group function...

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

Select allOpen in new window

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...

my last reply was to Saurabh Singh Teotia2015-03-13 at 14:26:12ID: 40664307..


Thank you slightwv I will try your solution,.,..

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

Select allOpen 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

Select allOpen in new window

Saurabh...

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!!

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...

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.

>>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;

Select allOpen in new window

>>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.

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..

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?

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..

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

Select allOpen 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

Select allOpen in new window

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

Saurabh...

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...

>>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?

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.

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.

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...

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.

>>Again i can't verify it fully right now

Take my test case and paste in into sqlfiddle.

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 ?

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.
.

>>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.

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!!

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

>>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

Select allOpen 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
/

Select allOpen in new window

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..