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

March 25th, 2008, 02:12 PM
|
Authorized User
|
|
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|

March 25th, 2008, 02:29 PM
|
Wrox Author
Points: 13,255, Level: 49 |
|
|
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
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
================================================== =========
|

March 25th, 2008, 02:52 PM
|
Authorized User
|
|
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

March 25th, 2008, 03:12 PM
|
Wrox Author
Points: 13,255, Level: 49 |
|
|
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
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
================================================== =========
|

March 25th, 2008, 04:04 PM
|
Authorized User
|
|
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

March 25th, 2008, 06:03 PM
|
Wrox Author
Points: 13,255, Level: 49 |
|
|
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
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
================================================== =========
|

March 25th, 2008, 06:54 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|

March 25th, 2008, 07:00 PM
|
Friend of Wrox
|
|
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
|
|
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
|

March 25th, 2008, 07:12 PM
|
Wrox Author
Points: 13,255, Level: 49 |
|
|
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
|
|
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
================================================== =========
|

March 25th, 2008, 07:36 PM
|
Friend of Wrox
|
|
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
|
|
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
|
Thread Tools |
Search this Thread |
|
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
 |