Solved

Sql (oracle) select distinct on two rows and order by count

Posted on 2014-04-16
27
2,154 Views
Last Modified: 2014-04-27
Hi
In one table i have two columns  'Names'  and 'Numbers' .
There are multiple rows for the unique  'Names'  and they can have with different  'Numbers'

eg

Names  |  Number |
John        1234
John        1234
John        1234
John        4567
John        6789

I want a distinct list of the 'Names' and a count of how many different 'Numbers' each name has ordered  highest to lowest so in example above

Names |  Count
John           3

There is also a stored date field so only want results for last 24 hours eg stored > sysdate -1.

Preferably though i would like to include the 'Numbers' field as well but still order by highest count first

eg
Names |  Numbers
John          1234
John          4567
John          6789
Steve        2312
Steve        4765

Probably a real simple one but combinations i tried didn't get results i thought they would.
0
Comment
Question by:stopm
  • 9
  • 8
  • 8
  • +1
27 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Try this:
select names, count(mynumber) from
( select distinct names,mynumber from tab1 where somedate_column >= trunc(sysdate-1)
)
group by names
/

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
oops...  missed some requirements.  The sample data threw me off.

I'm confused.

You first want the name and a count of distinct numbers then a list of names and distinct numbers.

Which is it?  I see these as two different selects.

Can you clarify?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
OK,  see if this test case gives you what you need.

It doesn't have the date logic but I provided that in the post above.

If there are problems, please add to the test case and explain what you are adding and why.

drop table tab1 purge;
create table tab1 (names varchar2(5), mynumber number);

insert into tab1 values('John',1234);
insert into tab1 values('John',1234);
insert into tab1 values('John',1234);
insert into tab1 values('John',4567);
insert into tab1 values('John',6789);
insert into tab1 values('John',5);
--
insert into tab1 values('Steve',2);
insert into tab1 values('Steve',3);
insert into tab1 values('Steve',3);
commit;



select names, max(number_count) from 
(
select names,
	mynumber,
	count(*) over(partition by names, mynumber order by mynumber) number_count
from tab1
)
group by names
order by 2 desc
/



select names, mynumber from
(
select distinct names, mynumber, number_count
from
(
select names,
	mynumber,
	count(*) over(partition by names, mynumber order by mynumber) number_count
from tab1
)
order by names,number_count desc
)
/

Open in new window

0
 
LVL 11

Expert Comment

by:John_Vidmar
Comment Utility
I'm not an Oracle expert, this works in MS SQL Server (using a derived-table):
SELECT DISTINCT
	a.Name
,	a.Number
,	b.RowCount
FROM	SomeTable	a
JOIN	(	SELECT	Name
		,	Number
		,	COUNT(*) as RowCount
		FROM	SomeTable
		GROUP
		BY	Name
		,	Number
	)	b	ON	a.Name = b.Name
			AND	a.Number = b.Number

OUTPUT:

	Name	Number	RowCount
	------	------	--------
	John	1234	3
	John	4567	1
	John	6789	1
	Steve	2312	1
	Steve	4765	1

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>I'm not an Oracle expert, this works in MS SQL Server (using a derived-table):

Syntax also appears to work in Oracle in my 11gR2 database.

It hits the table twice which could be an issue.  The examples I posted should only hit it once.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
This variant also works in 11g (no nesting)

SELECT DISTINCT
        name
      , mynumber
      , count(*) over(partition BY name, mynumber) AS number_count
FROM Table1
ORDER BY number_count DESC
      
      
but you do get all 3 columns returned

{+edit, order by isn't required in the count}
0
 

Author Comment

by:stopm
Comment Utility
Hi Slightwv,

So going back to your first sql will this one count blanks as i hadn't realised that some rows  have a Name but no Number. (i don't want to count the blanks)

eg

Names  Numbers
John       1234
John      
John       1234

As for the other question,  yes they are two different statements and my preferred one would be to return the names and the numbers with the Name that has the highest amount of combinations at the top of the list.

Names |  Numbers
John          1234
John          4567
John          6789
Steve        2312
Steve        4765

Just going to try the other select statement you sent.
Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
yes, nulls will be counted

Names  Numbers
John       1234
John      
John       1234

should produce

John <null> 1
John 1234 2
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>> (i don't want to count the blanks)

Add "and numbers is not null" to the inner query?

If the column contains spaces:
and trim(numbers) is not null
0
 

Author Comment

by:stopm
Comment Utility
Sorry for delay in replying- Easter break

Most of the above queries give me a total count of all rows in the table of Names and Numbers but  I need just the unique combinations of Names and Numbers ordered by the highest count of these first (as per example below count below 4,3,2)
Note:- The table has many more rows where the Names and the Numbers are identical but i don't need to know this number only the distinct pairings.

Eg:-

Names |  Numbers | Count of unique combinations
John          1234              4
John          4567              4
John          6789              4
John          9876              4
Steve        2312              3
Steve        4765              3
Steve        5678              3
Colin         7777              2
Colin         6666              2              

This query below from 'slightwv' does give me a count of the unique combinations of Names and Numbers but would be good if i can get as above and include the Numbers field as well or even just the Names and Numbers without the count field providing they are ordered in highest number of  distinct combinations descending:-

select  names, count(numbers) from ( select distinct name, numbers
from tab1 where numbers is not null))
group by names order by count(numbers) desc

Names |  Count
John           4
Steve         3
Colin          2

Thanks,  and hope this makes sense.
0
 

Author Comment

by:stopm
Comment Utility
Increased points as it looks a bit more complicated then i thought originally
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Sorry but I still don't understand.

Looks like a simple tweak to the second query in http:#a40005407 returns the numbers and count.

Change:
select names, mynumber from

To:
select names, mynumber, number_count from

Making that change with the sample data above I get:
NAMES   MYNUMBER NUMBER_COUNT
----- ---------- ------------
John        1234            3
John           5            1
John        4567            1
John        6789            1
Steve          3            2
Steve          2            1

Open in new window


Also looks like the example from John_Vidmar also shows similar results even though it hits the table twice.

Instead of trying to explain what you want please provide sample raw data and expected results.  Similar to the test case I posted.  It has the base table and sample data.  then all we need to see is the actual expected results.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
count() over( ... order by ..)
there is no point or need for order by inside that over(...)

and nesting of the query isn't required either because distinct is a row operator (it's applied after all rows are known)

 
    SELECT DISTINCT
            names
          , mynumber
          , count(*) over(partition BY names, mynumber) AS number_count
    FROM Tab1
    ORDER BY names, number_count DESC, mynumber ASC
    
    

**[Results][2]**:
    
    | NAMES | MYNUMBER | NUMBER_COUNT |
    |-------|----------|--------------|
    |  John |     1234 |            3 |
    |  John |        5 |            1 |
    |  John |     4567 |            1 |
    |  John |     6789 |            1 |
    | Steve |        3 |            2 |
    | Steve |        2 |            1 |



  [1]: http://sqlfiddle.com/#!4/0eddb/3

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:stopm
Comment Utility
Thanks slightwv

Ref your comment :-
Regarding the second query in http:#a40005407

Yes that query does return the results i want , except it does not order them by highest number of unique combinations 1st it orders them by the Names field descending.
 (In my table the Names are actually stored as digits to represent peoples names (eg John = 04, Steve =02 etc.. Thought it would be simpler to use names in example  but maybe that was not such a good idea!)
 
Sorry actual data from my table is a bit sensitive to post on line.

-------------------------------------------------------
Making these changes below only gave me count of the number of  rows which i am not interested in. eg I don't need to know that John 1234 has 3 entries. I only want it to be above Steve because it has 4 unique entries and Steve only has 2


NAMES   MYNUMBER NUMBER_COUNT
----- ---------- ------------
John        1234            3


Change:
select names, mynumber from

To:
select names, mynumber, number_count from

Thanks again
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>" except it does not order them by highest number of unique combinations 1st it orders them by the Names field descending."

a. that is trivially easy to fix, just alter the order by clause to suit
    SELECT DISTINCT
            names
          , mynumber
          , count(*) over(partition BY names, mynumber) AS number_count
    FROM Tab1
    ORDER BY number_count DESC, names, mynumber ASC -- change line this to suit

Open in new window

, and

b. that is what you actually asked for in the question: the provided list is ordered by name first
Preferably though i would like to include the 'Numbers' field as well but still order by highest count first

eg
Names |  Numbers
John          1234
John          4567
John          6789
Steve        2312
Steve        4765
0
 

Author Comment

by:stopm
Comment Utility
Hi PotrletPaul

Your query is returning a count of all the rows as opposed to only a count of the unique combinations of Names and Numbers
eg.  There are thousands of rows  for John 123, John 4567 and John 6789  in the table but i don't need a count of all  these rows i just need a count of the unique parings , in this example would = 3.  
I only ever  wanted the list ordered by highest number of these unique combinations 1st    but grouped together by Names.   So if  'Steve'  has got more unique combinations then 'John'  put all of 'Steve's' unique combinations at the top of the list above 'John's'

Sorry if that was not clear in my statement

Thanks
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Please:  As I posted in http:#a40013846

Instead of trying to explain what you want please provide sample raw data and expected results.  Similar to the test case I posted.  It has the base table and sample data.  then all we need to see is the actual expected results.

Given all your explanations once we see the raw data along with the expected results we'll be able to post a working test case.
0
 

Author Comment

by:stopm
Comment Utility
Sorry as i said earlier i cant post the raw data on line.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Sorry as i said earlier i cant post the raw data on line.

I'm not asking for real data.  Just dummy data that is close enough to your actual structure that when we post working SQL, you can apply it to your data.

You've provided dummy expected results in just about every post but  you have never provided the raw dummy data to go with it.

What I am looking for is a sample test case like I posted in http:#a40005407

It has a table and sample data.  The only thing I'm missing is:
Given that table and data, what results do you want from it.

Make it up but make it close to your actual situation.

For example say you are really working with payroll data and have:
table employee_pay(ssn varchar2(9), salary number)
111-22-3333, 10000000
666-44-9999, 10
...

Can you not post a dummy table here:
table fuzzy_pickle(flavor varchar2(9), calories number)
dill, 10
kohser, 500
...

Same table structure, same data types, similar data.  All you need to do is provide the expected results from the data you provide.

Then any query against fuzzy_pickle would run against your salary table.  Just change the names.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 350 total points
Comment Utility
OK, in this sample data I repeated each name/number pair 3 times, but the queries count only the unique combinations. This is done by either by a group by, or using select distinct, then counting over each name as the partition.
data:
    CREATE TABLE Table1
    	(NAMES varchar2(5), NUMBERS int)
    ;
    
    INSERT ALL 
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 1234)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 4567)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 6789)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 9876)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 2312)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 4765)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 5678)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 7777)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 6666)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 1234)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 4567)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 6789)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 9876)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 2312)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 4765)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 5678)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 7777)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 6666)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 1234)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 4567)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 6789)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('John', 9876)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 2312)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 4765)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Steve', 5678)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 7777)
    	INTO Table1 ("NAMES", "NUMBERS")
    		 VALUES ('Colin', 6666)
    SELECT * FROM dual
    ;

Open in new window

Queries (2 alternatives)
    SELECT
          names
        , numbers
        , COUNT(*) OVER (PARTITION BY names) AS combinantion_count
    FROM (
                SELECT
                      names
                    , numbers
                FROM table1
                GROUP BY
                      names
                    , numbers
          )
    ORDER BY
          combinantion_count DESC, names ASC
    

**[Results][2]**:
    
    | NAMES | NUMBERS | COMBINANTION_COUNT |
    |-------|---------|--------------------|
    |  John |    6789 |                  4 |
    |  John |    9876 |                  4 |
    |  John |    4567 |                  4 |
    |  John |    1234 |                  4 |
    | Steve |    5678 |                  3 |
    | Steve |    2312 |                  3 |
    | Steve |    4765 |                  3 |
    | Colin |    7777 |                  2 |
    | Colin |    6666 |                  2 |


**Query 2**:

    SELECT
          names
        , numbers
        , COUNT(*) OVER (PARTITION BY names) AS combinantion_count
    FROM (
                SELECT DISTINCT
                      names
                    , numbers
                FROM table1
          )
    ORDER BY
          combinantion_count DESC, names ASC
    

**[Results][3]**:
    
    | NAMES | NUMBERS | COMBINANTION_COUNT |
    |-------|---------|--------------------|
    |  John |    9876 |                  4 |
    |  John |    1234 |                  4 |
    |  John |    4567 |                  4 |
    |  John |    6789 |                  4 |
    | Steve |    5678 |                  3 |
    | Steve |    2312 |                  3 |
    | Steve |    4765 |                  3 |
    | Colin |    7777 |                  2 |
    | Colin |    6666 |                  2 |



  [1]: http://sqlfiddle.com/#!4/b2003/9

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
See?  Paul's post above has table, data and results.  nothing in it is 'real'.

If that post isn't what you are after, take that example, add or remove data from it and provide the results you are after.

Don't explain 'why' it doesn't work, just post the results as you want to see them.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
utterly agree with slightwv

you have implied sample data, but not actually provided any
e.g. the result list at comment 40013818

(which is what I have based my latest queries on)

e.g. the following would have been clear, with less words

Sample Data:
Table1
NAMES        NUMBERS
Colin        6666
Colin        6666
Colin        6666
Colin        7777
Colin        7777
Colin        7777
John         1234
John         1234
John         1234
John         4567
John         4567
John         4567
John         6789
John         6789
John         6789
John         9876
John         9876
John         9876
Steve        2312
Steve        2312
Steve        2312
Steve        4765
Steve        4765
Steve        4765
Steve        5678
Steve        5678
Steve        5678

Expected Result:
Names |  Numbers | Count of unique combinations
John          1234              4
John          4567              4
John          6789              4
John          9876              4
Steve        2312              3
Steve        4765              3
Steve        5678              3
Colin         7777              2
Colin         6666              2      

assuming of course that I have interpreted comment 40013818 correctly
0
 

Author Comment

by:stopm
Comment Utility
Many Thanks PortletPaul your a star,  both your query's look to work perfectly but still doing some testing to confirm.
Much appreciated
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Thanks for the award of points, but you have graded the answer as B. Quite frankly I cannot possibly see how that was warranted.
What grade should I award?

A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
Did the answer lack information?
Did you have to perform extra work to get it to work for you?
Did you explain why you chose the B grading?

(And by the way, you have flagged the wrong comment as the accepted answer, which should have been comment ID: 40016346)

I see you quite often award B's and C's
You really need to read that article above. If you feel a question is simple to answer, adjust the points downward. You should not use grading for scaling the percieved difficulty.

I do realise reaching the answer probably took longer than you expected, but hopefully we have not only explained why that was true, but also shown you techniques to express your requirements in future.
0
 

Author Comment

by:stopm
Comment Utility
Hi SouthMod

After reading the Grade Award info i can see that i should have given this question an A grade, as the answer  did fulfil all requirements.

Not sure if you can change retrospectively?
Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
@stopm

SouthMod did alter set the accepted answer to  ID: 40016346 and the grading to A so there's no more to be done, but thank you for agreeing to that change.

If you did need to change something you may "Request Attention" (a link you will find under the question) and ask the moderators for assistance.

Paul
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now