Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 25th, 2008, 02:12 PM
Authorized User
Points: 254, Level: 5
Points: 254, Level: 5 Points: 254, Level: 5 Points: 254, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select .. Top 10 by Month?

Lets say I have a table, named t_savings, with 3 fields (Month, Name, and Saving). And with every month, there's 100 people being tracked.

Now lets say I want the Top 10 Savers by month. Is there a easy way to do this?

I know "select top 10 * from t_savings order by saving desc" will give me the top 10, but I want it based by month also but don't want to have to have a separate query for every month.

Thanks!



Reply With Quote
  #2 (permalink)  
Old March 25th, 2008, 02:29 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I just ran this query against one of my tables, it should work for you:

SELECT top 10 id, url, DatePart(month, Last_Updated) from menus order by DatePart(month, Last_Updated) desc, id asc


This gave me results like this:

ID URL MONTH
7 foo 3
8 foo 3
9 foo 3
1 foo 2
2 foo 2
etc...

Does this work for you?

[edit]
Actually you will probably want a query similar to this instead:

SELECT top 10 id, url, datepart(month, Last_Updated) from menus order by DatePart(month, Last_Updated)desc, id desc

Which gives results like this:
ID URL MONTH
9 foo 3
8 foo 3
7 foo 3
2 foo 2
1 foo 2
etc...

So if the ID column where the amount column it is always going highest to lowest based on descending month.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #3 (permalink)  
Old March 25th, 2008, 02:52 PM
Authorized User
Points: 254, Level: 5
Points: 254, Level: 5 Points: 254, Level: 5 Points: 254, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It only give me the Top 10.

Here's what I'm trying to get, but lets use Top 3

MONTH NAME SAVINGS
200801 JOHN 500
200801 MIKE 250
200801 JANE 50
200802 JOHN 500
200802 SHANE 400
200802 MIKE 350
200803 JANE 700
200803 JOHN 500
200803 MARK 200

The problem is, when I do:
select top 3 from T_SAVINGS order by SAVING

MONTH NAME SAVINGS
200803 JANE 700
200803 JOHN 500
200802 JOHN 500

What I'm trying to do is get the 1st set of results without having to do a query for every month and doing unions.

Thanks





Reply With Quote
  #4 (permalink)  
Old March 25th, 2008, 03:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Your original post used the example of top 10 hence my examples used top 10.

Now I don't seem to understand your problem. Quickly I typed up this code:

DECLARE @tbl table ([Month] datetime, sname varchar(50), Savings int)
INSERT INTO @tbl([month], sname, savings)
Values('01/01/2008', 'John', 500)

INSERT INTO @tbl([month], sname, savings)
Values('01/01/2008', 'Mike', 250)

INSERT INTO @tbl([month], sname, savings)
Values('01/01/2008', 'Jane', 50)

INSERT INTO @tbl([month], sname, savings)
Values('02/01/2008','John', 500)

INSERT INTO @tbl([month], sname, savings)
Values('02/01/2008', 'Shane',400)

INSERT INTO @tbl([month], sname, savings)
Values('02/01/2008', 'Mike',350)

INSERT INTO @tbl([month], sname, savings)
Values('03/01/2008','Jane', 700)

INSERT INTO @tbl([month], sname, savings)
Values('03/01/2008','John', 500)

INSERT INTO @tbl([month], sname, savings)
Values('03/01/2008','Mark', 200)

SELECT Top 3 [month], sname, savings from @tbl order by DatePart(Month, [Month]) desc, savings desc

My resultset was

MONTH Sname Savings
2008-03-01 Jane 700
2008-03-01 John 500
2008-03-01 Mark 200

Is this not what you want??

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #5 (permalink)  
Old March 25th, 2008, 04:04 PM
Authorized User
Points: 254, Level: 5
Points: 254, Level: 5 Points: 254, Level: 5 Points: 254, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm trying to get this based on the Top 3 because I want the Top by Month.

MONTH NAME SAVINGS
200801 JOHN 500
200801 MIKE 250
200801 JANE 50
200802 JOHN 500
200802 SHANE 400
200802 MIKE 350
200803 JANE 700
200803 JOHN 500
200803 MARK 200

The only way I can think of doing this is using a union query such as the below.

select top 3 MONTH, NAME, SAVING from T_SAVINGS where MONTH = '200801' order by SAVING desc
union
select top 3 MONTH, NAME, SAVING from T_SAVINGS where MONTH = '200802' order by SAVING desc
union
select top 3 MONTH, NAME, SAVING from T_SAVINGS where MONTH = '200803' order by SAVING desc
etc......... for all 12 months.

I'm looking for a short cut to get the top 3 or top 10 by month.



Reply With Quote
  #6 (permalink)  
Old March 25th, 2008, 06:03 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Hmm this is probably a question for one of the higher level SQL Gurus like Peso or Scott because I am not sure how you would do this as a one shot query without using Unions.

Sorry I can't be of more help.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #7 (permalink)  
Old March 25th, 2008, 06:54 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

CrabJoe...

The problem is that you keep describing what you want for a result instead of showing folks and they're not getting it.

Also, if you want faster answers that can easily be tested, you need to create a table and some data like DParsons did... people hate converting poorly formed data on a forum to something they can test with... it just takes to0 bloody long.

With all of that in mind, here's what your post should have looked like for table information and data... and, the answer is also there.

Code:
--===== Create a demo table for the example.  Don't use
     -- table variables... they don't persist for trouble
     -- shooting.  Also notice that the primary key is
     -- indicated.
 CREATE TABLE #MyTable
        (
        RowNum  INT IDENTITY(1,1) PRIMARY KEY,
        Date    DATETIME, 
        SName   VARCHAR(50), 
        Savings INT
        )

--===== Populate the table with some reasonable test data
 INSERT INTO #MyTable
        (Date,SName,Savings)
 SELECT '20080101', 'John'  , 500 UNION ALL
 SELECT '20080101', 'Juliet', 250 UNION ALL
 SELECT '20080101', 'Jane'  , 50  UNION ALL
 SELECT '20080101', 'Joe'   , 100 UNION ALL
 SELECT '20080101', 'Jeff'  , 10  UNION ALL
 SELECT '20080101', 'Jamie' , 400 UNION ALL

 SELECT '20080215', 'John'  , 5  UNION ALL
 SELECT '20080215', 'Juliet', 10 UNION ALL

 SELECT '20080302', 'John'  , 10 UNION ALL
 SELECT '20080302', 'Juliet', 20 UNION ALL
 SELECT '20080302', 'Jane'  , 30 UNION ALL
 SELECT '20080305', 'Joe'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080315', 'Jay'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080325', 'Jan'   , 60 UNION ALL --3 way tie for 2nd
 SELECT '20080302', 'Jeff'  , 50 UNION ALL
 SELECT '20080302', 'Jamie' , 100 

 --===== And, here's your solution and it works
     -- across years 
 SELECT YEAR(t1.Date) AS Year,
        Month(t1.Date) AS Mo,
        t1.SName,
        t1.Savings
   FROM #MyTable t1
  WHERE t1.RowNum IN (SELECT TOP 3 WITH TIES t2.RowNum
                        FROM #MyTable t2 
                       WHERE DATEDIFF(mm,0,t1.Date) = DATEDIFF(mm,0,t2.Date) 
                       ORDER BY t2.Savings DESC)
  ORDER BY Year, Mo, t1.Savings DESC

DROP TABLE #MyTable
... and that creates an output like this...

Code:
Year        Mo          SName                                              Savings     
----------- ----------- -------------------------------------------------- ----------- 
2008        1           John                                               500
2008        1           Jamie                                              400
2008        1           Juliet                                             250
2008        2           Juliet                                             10
2008        2           John                                               5
2008        3           Jamie                                              100
2008        3           Joe                                                60
2008        3           Jay                                                60
2008        3           Jan                                                60
Any questions on any of that? ;)

--Jeff Moden
Reply With Quote
  #8 (permalink)  
Old March 25th, 2008, 07:00 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

P.S. The correlated subquery would be replaced by a CTE with a ROW_NUMBER() or RANK function in it in SQL Server 2005.

--Jeff Moden
Reply With Quote
  #9 (permalink)  
Old March 25th, 2008, 07:12 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Marvelous example Jeff. Nicely done. =]

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========
Reply With Quote
  #10 (permalink)  
Old March 25th, 2008, 07:36 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

Hey Jeff,

Excellent example and solution. I was wondering about the WITH TIES clause. Without it the query still pulls Jay at 60 and Joe at 60 but not Jan at 60. Why is that?

Thanks,
Richard

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seek help for select Month in list box apple88 Classic ASP Databases 1 October 11th, 2007 01:11 PM
query Current Month, Month+1, Month+2, Month+3 anterior Access 2 September 24th, 2006 08:25 PM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
Crystal reports 10.0 Query about showing top N rec rajdotme Crystal Reports 1 June 12th, 2004 03:35 AM
Retrieve top 10 bmains XSLT 1 January 5th, 2004 02:54 PM



All times are GMT -4. The time now is 10:48 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.