Solved

To show the 50 highest results from SQL query

Posted on 2014-10-22
16
271 Views
Last Modified: 2014-10-22
I only want to show the 50 highest results from the count(*0 as Total column of this query

select  SO.PART_ID, count(*) as Total, COUNT(*)/ sum(count(*)) over()
from CSI.SO_PICK_DETAIL SO
where  SO.DC_ID = '80'


 and SO.end_DATE between @variable('Date Begining:') and @variable('Date Ending:')

and SO.ARCHIVE_DATE BETWEEN TO_DATE(@variable('Date Begining:'),'YYYY-MM-DD') + 1
and TO_DATE(@variable('Date Ending:'),'YYYY-MM-DD') + 1

Group By SO.PART_ID
order by Total desc
0
Comment
Question by:Jbancr1
  • 6
  • 6
  • 2
  • +1
16 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40397335
Try this --> ORDER BY count(*) DESC

>@variable('Date Begining:') and @variable('Date Ending:')
Curiosity overwhelms me ... Why have spaces and punctuation marks in variable names?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40397338
In Oracle:

select * from (
--your select here
)
where rownum<51
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40397354
Maybe you can use RANK

select  TOP (50)  part_id, total, RANK () over (order by total) as rankbytotal
from(
select SO.PART_ID, count(*) as Total, COUNT(*)/ sum(count(*)) over()
from CSI.SO_PICK_DETAIL SO
where  SO.DC_ID = '80'
 and SO.end_DATE between @variable('Date Begining:') and @variable('Date Ending:')
and SO.ARCHIVE_DATE BETWEEN TO_DATE(@variable('Date Begining:'),'YYYY-MM-DD') + 1
and TO_DATE(@variable('Date Ending:'),'YYYY-MM-DD') + 1
Group By SO.PART_ID
)a
order by  part_id
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Jbancr1
ID: 40397435
I get an ORA-00904: "TOP"; invalid identfier :-904 error
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397439
TOP isn't Oracle.  It is SQL Server.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40397448
i saw the MS SQL topic and then i used the syntax for MS SQL try this:

select  part_id, total,  RANK() OVER (PARTITION BY part_id    ORDER BY part_id )  as rankbytotal
from(
select SO.PART_ID, count(*) as Total, COUNT(*)/ sum(count(*)) over()
from CSI.SO_PICK_DETAIL SO
where  SO.DC_ID = '80'
 and SO.end_DATE between @variable('Date Begining:') and @variable('Date Ending:')
and SO.ARCHIVE_DATE BETWEEN TO_DATE(@variable('Date Begining:'),'YYYY-MM-DD') + 1
and TO_DATE(@variable('Date Ending:'),'YYYY-MM-DD') + 1
Group By SO.PART_ID
)a
where rownum<50
order by  part_id
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397455
>>try this:

The original query already has "order by Total desc ".

I believe all that is needed is wrapping the query and use rownum.  I don't see what RANK adds to the solution.
0
 

Author Comment

by:Jbancr1
ID: 40397456
This only gives me the first 50 lines it doesn't give me the 50 highest totals.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397464
>>This only gives me the first 50 lines it doesn't give me the 50 highest totals.

What "this" are you referring to?

Mine will give you the first 50 of whatever the order is in the inner select.
0
 

Author Comment

by:Jbancr1
ID: 40397478
This query sample only give me the first 50 lines of the results noth ethe 50 highest results of totals

select  part_id, total,  RANK() OVER (PARTITION BY part_id    ORDER BY part_id )  as rankbytotal
 from(
 select SO.PART_ID, count(*) as Total COUNT(*)/ sum(count(*)) over()
from CSI.SO_PICK_DETAIL SO
 where  SO.DC_ID = '80'
  and SO.end_DATE between @variable('Date Begining:') and @variable('Date Ending:')
and SO.ARCHIVE_DATE BETWEEN TO_DATE(@variable('Date Begining:'),'YYYY-MM-DD') + 1
 and TO_DATE(@variable('Date Ending:'),'YYYY-MM-DD') + 1
 Group By SO.PART_ID
 )a
 where rownum<50
 order by  part_id
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397484
Try mine re: http:#a40397338
0
 

Author Comment

by:Jbancr1
ID: 40397504
slightwv your link just takes me to the top of the page.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40397508
>>slightwv your link just takes me to the top of the page.

It takes you to the top of the page and my first post.

In that post I have:
select * from (
 --your select here
 )
 where rownum<51
0
 

Author Comment

by:Jbancr1
ID: 40397515
Dah yea I'm stupid that is what you meant. After my senior moment I realized what you meant and tried your suggestion and it worked. It gave me what I was looking for.
0
 

Author Closing Comment

by:Jbancr1
ID: 40397527
Thanks for your help my stupidity.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

832 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