Solved

To show the 50 highest results from SQL query

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
return result by latest date - oracle query 21 69
Oracle SQL Select within a Where Clause 9 56
Wrap Oraccle SQL*Plus executable Command 4 68
constraint check 2 13
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to recover a database from a user managed backup

910 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

21 Experts available now in Live!

Get 1:1 Help Now