[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Top 20 produces Top 20

Posted on 2009-05-15
8
Medium Priority
?
171 Views
Last Modified: 2012-05-07
The code below produces my desired result of using the Row_Number () function to Rank a number of agents within  their Sales Territory (Partitioned by Terr) by each Line of business (2nd Partition Line) using Items Sold to Order them from high to low (best to worse within each line). There are 5 Territories and 5 Liness of business.

The query works as there are about 1200 agents with 5 lines of business and my result contains 6000 rows all in descending order by Terrr. I am happy. However, I am being ask to supply only the Top 20 for each terr by line of business. So I tried to a Select Top 20 and that returns the Top 20 rows in the query.

Any suggestions, perhaps a stored procedure?
 
SELECT     TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS Rank, Terr, Mkt, AgtNo, Items,  Line

Open in new window

0
Comment
Question by:SeTech
[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
  • 5
  • 3
8 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24398574
select * from
(
SELECT     TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS Rank, Terr, Mkt, AgtNo, Items,  Line
From SOMETABLE
)a
where [Rank] >= 20
0
 

Author Comment

by:SeTech
ID: 24398757
Brandon - So I kept trying and found that a CTE works do the
with top as (SELECT     TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS Rank, Terr, Mkt, AgtNo, Items,  Line)
Select Rank, Terr, Mkt, AgtNo, Items,  Line
from Top
where Rank <= 20

*** Similar to your solutions, which I think is a dervied table when using a select in the from ***
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24398906
Correct.  You can use a CTE or a derived table.  The cases in which I would use a CTE is if you have the need to reference the CTE more than one time in the select statement.  Otherwise, I would use a derived table as I have done.  

For your case, they do exactly the same.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:SeTech
ID: 24399114
Brandon - thanks much - I have encountered an issue with the CTE. I have it in a view (SQL 2005) and it will not save the With Nmae As portion of the CTE. This being the very begining prior to the  (Select. So what happens is I save and loose that portion. Any ideas?

Thank you
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24399157
In general, it is a bad idea to use SQL Server key words, or reserved words, in your object/column names.

The following in the below code violate that.
rank - column name
top - CTE name

You can code around this by encapsulating it in brackets [] or by changing the name.  If you aren't SET on these names yet, I suggest changing it and using my first query below.  If you must, then use the second.

I changed the name of the CTE from top to topItems as it is not referenced outside of the view.
;with topItems as (SELECT     TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS theRank, Terr, Mkt, AgtNo, Items,  Line)
Select theRank, Terr, Mkt, AgtNo, Items,  Line
from topItems
where theRank <= 20
 
 
;with topItems as (SELECT     TOP 100 PERCENT Row_Number() OVER (Partition BY Terr, Line
ORDER BY Items DESC) AS Rank, Terr, Mkt, AgtNo, Items,  Line)
Select [Rank], Terr, Mkt, AgtNo, Items,  [Line]
from topItems
where Rank <= 20

Open in new window

0
 

Author Comment

by:SeTech
ID: 24399398
I understand about the key words and corrected. However the problem is (and using your code) the with topItems as - this part of the code does NOT save when I close my view. If I use the ; in fornt it will not parse and kicks me out. ???
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 24399424
There is no reason to use a CTE since you aren't attempting to JOIN the CTE to itself.  I would just use a derived table.

If you must, I would create the view with the query tool and not the "new view" tool
Create View YOURVIEWNAME
as
;with topItems as (SELECT Row_Number() OVER (Partition BY Terr, Line ORDER BY Items DESC) AS theRank, Terr, Mkt, AgtNo, Items,  Line
From <ENTERYOURTABLENAMEHERE>)
Select theRank, Terr, Mkt, AgtNo, Items,  Line
from topItems
where theRank <= 20
 

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24399433
Also, I see you are doing top 100 percent.  I hope you weren't doing that in an attempt to ORDER within your view.  I've written up an article on why that doesn't work if you have some time to read.

http://sqlservernation.com/blogs/brandongalderisi/archive/2009/03/15/when-ordering-in-a-view-doesn-t-work.aspx
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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