Select .. Top 10 by Month?

crabjoe · #1 (**permalink**) March 25th, 2008, 02:12 PM

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!

dparsons · #2 (**permalink**) March 25th, 2008, 02:29 PM

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
================================================== =========

crabjoe · #3 (**permalink**) March 25th, 2008, 02:52 PM

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

dparsons · #4 (**permalink**) March 25th, 2008, 03:12 PM

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
================================================== =========

crabjoe · #5 (**permalink**) March 25th, 2008, 04:04 PM

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.

dparsons · #6 (**permalink**) March 25th, 2008, 06:03 PM

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
================================================== =========

Jeff Moden · #7 (**permalink**) March 25th, 2008, 06:54 PM

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

Jeff Moden · #8 (**permalink**) March 25th, 2008, 07:00 PM

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

dparsons · #9 (**permalink**) March 25th, 2008, 07:12 PM

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
================================================== =========

rstelma · #10 (**permalink**) March 25th, 2008, 07:36 PM

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

Need to download code?

Navigation