Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

To show the 50 highest results from SQL query

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
Jbancr1
Asked:
Jbancr1
  • 6
  • 6
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
slightwv (䄆 Netminder) Commented:
In Oracle:

select * from (
--your select here
)
where rownum<51
0
 
Haris DjulicCommented:
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
Technology Partners: 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!

 
Jbancr1Author Commented:
I get an ORA-00904: "TOP"; invalid identfier :-904 error
0
 
slightwv (䄆 Netminder) Commented:
TOP isn't Oracle.  It is SQL Server.
0
 
Haris DjulicCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Jbancr1Author Commented:
This only gives me the first 50 lines it doesn't give me the 50 highest totals.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
Jbancr1Author Commented:
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
 
slightwv (䄆 Netminder) Commented:
Try mine re: http:#a40397338
0
 
Jbancr1Author Commented:
slightwv your link just takes me to the top of the page.
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
Jbancr1Author Commented:
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
 
Jbancr1Author Commented:
Thanks for your help my stupidity.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now