p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Select .. Top 10 by Month? (http://p2p.wrox.com/showthread.php?t=66885)

crabjoe March 25th, 2008 02:12 PM

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!




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



All times are GMT -4. The time now is 08:32 PM.

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