Solved

To show the 50 highest results from SQL query

Posted on 2014-10-22
16
268 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
 

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

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Salary Amount Format 13 56
Pl/SQL Query 31 61
VB.Net - CSV to Oracle table 4 29
dates - loop 12 41
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

706 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

17 Experts available now in Live!

Get 1:1 Help Now