SQL Server Forums
Profile | Register | 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		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amurark
Yak Posting Veteran

India
50 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
47023 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
50 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
47023 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
50 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
50 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
22460 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  
 New 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.06 seconds. Powered By: Snitz Forums 2000