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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 maximum 5 values of nodes month wise
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amurark
Yak Posting Veteran

India
55 Posts

Posted - 12/26/2012 :  22:22:10  Show Profile  Reply with Quote
pls tell me how to get 5 maximum values month wise


Node time Maximum
A 1/12/2012 30
B 2/12/2012 40
C 3/12/2012 67
D 4/12/2012 87
E 5/12/2012 89
G 6/12/2012 92
F 7/12/2012 12
H 8/12/2012 345
I 9/12/2012 876
J 10/12/2012 589
K 11/12/2012 123
L 12/12/2012 456
M 13/12/2012 989
N 14/12/2012 75
O 15-12-2012 69
P 16-12-2012 86
Q 17-12-2012 47
R 18-12-2012 100
S 19-12-2012 101
T 20-12-2012 149
U 21-12-2012 777
V 22-12-2012 989
W 23-12-2012 340
X 24-12-2012 345
Y 25-12-2012 2323
Z 26-12-2012 340
A 1/12/2013 334
B 2/12/2013 400
C 3/12/2013 671
D 4/12/2013 123
E 5/12/2013 2323
G 6/12/2013 334
F 7/12/2013 3434
H 8/12/2013 99
I 9/12/2013 989
J 10/12/2013 8987
K 11/12/2013 87
L 12/12/2013 87
M 13/12/2013 8987
N 14/12/2013 465
O 15-12-2013 786
P 16-12-2013 345
Q 17-12-2013 123
R 18-12-2013 100
S 19-12-2013 101
T 20-12-2013 98
U 21-12-2013 999
V 22-12-2013 1000
W 23-12-2013 10000
X 24-12-2013 37
Y 25-12-2013 34
Z 26-12-2013 78
A 1/1/2012 34
B 2/1/2012 56
C 3/1/2012 78
D 4/1/2012 98
E 5/1/2012 876
G 6/1/2012 156
F 7/1/2012 78
H 8/1/2012 99
I 9/1/2012 99
J 10/1/2012 100
K 11/1/2012 12
L 12/1/2012 34
M 13/1/2012 45
N 14/1/2012 32
A 1/2/2012 10
B 2/2/2012 90
C 3/2/2012 78
D 4/2/2012 96
E 5/2/2012 57
F 6/2/2012 84
G 7/2/2012 34
H 8/2/2012 87
I 9/2/2012 108
J 10/2/2012 129
K 11/2/2012 987
L 12/2/2012 14
A 1/3/2012 18
B 2/3/2012 100
C 3/3/2012 987
D 4/3/2012 122
E 5/3/2012 234
F 6/3/2012 343
G 7/3/2012 45
H 8/3/2012 21
I 9/3/2012 1245
J 10/3/2012 33
K 11/3/2012 55
L 12/3/2012 79




Ankita

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/26/2012 :  23:24:39  Show Profile  Reply with Quote
sounds like this


SELECT Node,[time],Maximum
FROM
(
SELECT *,DENSE_RANK() OVER (PARTITION BY DATEADD(mm,DATEDIFF(mm,0,[time]),0) ORDER BY Maximum DESC) AS Seq
FROM Table
)t
WHERE Seq <=5


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

amurark
Yak Posting Veteran

India
55 Posts

Posted - 12/27/2012 :  00:11:31  Show Profile  Reply with Quote
Hi Visakh16
thnaks alot or your expert answers .
Pls check i am recieving sytax errror

as
missng operator in query expression 'dense over () '

Ankita
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 12/27/2012 :  00:12:43  Show Profile  Reply with Quote
its not dense its DENSE_RANK

B/w are you using sql server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

amurark
Yak Posting Veteran

India
55 Posts

Posted - 12/27/2012 :  00:25:10  Show Profile  Reply with Quote
no rite now i took excel as my database for testing purpose

Ankita
Go to Top of Page

amurark
Yak Posting Veteran

India
55 Posts

Posted - 12/27/2012 :  01:41:05  Show Profile  Reply with Quote
Please ex[palin me the meaning also of this query Please .



Ankita
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 12/27/2012 :  08:32:55  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer this
http://beyondrelational.com/modules/2/blogs/70/posts/10845/return-top-n-rows.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000