Top (n) by Select field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Top (n) by Select field

Author

Topic

dbutler_05
Starting Member

17 Posts

Posted - 2006-03-03 : 16:06:35

I have the following query that is returning the top 10 records.

Select Top(10) a.SelClass, sum(a.YTDNetSales - PYytdNetSales) as Variance, a.YTDNetSales as YTDSales, b.PYytdNetSales as PYYTDSales, c.cname, a.SelBrandGroup

From dbo.MonthEnd_YTDNetSales as A INNER JOIN
dbo.MonthEnd_YTDNetSales_PY AS B ON A.SelCusno = B.SelCusno and a.SelPtyp2 = b.SelPtyp2 and a.SelClass = B.SelClass inner join
dbo.mscmp100 as C on b.SelCusno = c.Cusno

Where A.CountryCd = '10' and B.countryCd = '10' and a.SelPtyp2 = 'BO'

Group by a.SelClass, a.SelBrandGroup, c.cname, ytdNetSales, PYytdNetSales
Order by Variance desc, a.SelClass

What I really need is the Top 10 for each SelClass. Any Suggestions? Below are some results examples:

What is being returned:
V 122997.5 323998.3 201000.8 NATIONAL VISION INC. A
V 64497.25 62202.25 -2295 COSTCO WHOLESALE A
V 21712.8 23547.1 1834.3 HINDA DISTRIBUTORS INC A
E 18115 -18400 -36515 COSTCO A
D 12960.9 11892 -1068.9 BOB WARD & SON, INC.*NBS* A
D 12541.06 10299.46 -2241.6 PIONEER SPORTS A
V 8747.5 9037.5 290 SELECT OPTICAL A
D 7690.75 7776.55 85.8 RECREATIONAL EQPMNT INC. A
D 7262.9 10912 3649.1 OKEMO MOUNTAIN SKI SHOP A
V 5764.5 7103.25 1338.75 CARLSON MKTG-PROMO DIV. A

What I need:
V 122997.5 323998.3 201000.8 NATIONAL VISION INC. A
V 64497.25 62202.25 -2295 COSTCO WHOLESALE A
V 21712.8 23547.1 1834.3 HINDA DISTRIBUTORS INC A
V 18115 -18400 -36515 COSTCO A
V 12960.9 11892 -1068.9 BOB WARD & SON, INC.*NBS* A
E 122997.5 323998.3 201000.8 NATIONAL VISION INC. A
E 64497.25 62202.25 -2295 COSTCO WHOLESALE A
E 21712.8 23547.1 1834.3 HINDA DISTRIBUTORS INC A
E 18115 -18400 -36515 COSTCO A
E 12960.9 11892 -1068.9 BOB WARD & SON, INC.*NBS* A

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-03 : 16:14:39

If you want help, you should really try to explain what it is you are trying to do. It is not obvious from the code you posted.

What version of MS SQL Server are you working with?

CODO ERGO SUM

dbutler_05
Starting Member

17 Posts

Posted - 2006-03-03 : 16:19:25

I thought I did. I need to Return the top 10 rows based on a field in my select statement. In other words, 10 rows of SelClass (10 rows of V, 10 rows of E, etc..) Right now I just get the top 10 rows that meet the query criteria. Does that make sense?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-03 : 16:33:59

http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx
point 2

Go with the flow & have fun! Else fight the flow

dbutler_05
Starting Member

17 Posts

Posted - 2006-03-03 : 16:41:45

That should do it. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources