Solved

To show the 50 highest results from SQL query

Posted on 2014-10-22
16
273 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 77

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

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 77

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 77

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 77

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 77

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

749 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