.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

Home >> Forum >> VB.Net >> Post New QuestionBookmark and Share Subscribe to Forum

how to do rank calculation

Posted By: vidhya     Posted Date: November 10, 2010    Points:2   Category :VB.Net
am preparing a student marklist in vb.net,
How to calculate Rank from (total,result(pass or fail)(pass))wise,
I already calculated tolal and result, Can anyone please send rank calculation coding...

i use this database

stuname nvarchar,class nvarchar,sec nvarchr,subject1 nvarchar,subject2 nvarchar,subject3 nvarchar,subject4 nvarchar,subject5 nvarchar, total nvarchar,result nvarchar,Rank nvarchar
the o/p is beow,......the Rank is null value how to put a rank in total wise

AdmNo StudentName Medium Standard Section Nameofexam Month Year Tamil English Maths Science Socialscience Total Result Rank
01 banu English 6thStd A Monthly Apr 2010 77 77 87 67 66 374 Pass null
02 priya English 6thStd A Monthly Apr 2010 56 55 55 66 76 308 Pass null
03 c English 6thStd C Monthly Apr 2010 34 55 44 43 44 220 Fail null
04 b English 6thStd D QuotallyApr 2010 67 86 66 66 77 362 Pass null
08 ashok English 6thStd A Monthly Apr 2010 55 45 33 55 44 232 Fail null


how to get a result
id total rank
01 400 1
02 380 2
03 370 3
in this order

Responses
Author: Suresh             
Posted Date: November 10, 2010     Points: 5   

Hello Vidhya,
Kindly try below one. You will get ur expected result. Let me know that do you need any further clarification.


declare @t table
(
AdmNo nvarchar(10),
StudentName nvarchar(100),
Medium nvarchar(100),
Standard nvarchar(100),
Section nvarchar(100),
Nameofexam nvarchar(100),
[Month] nvarchar(100),
[Year] nvarchar(100),
Tamil nvarchar(100),
English nvarchar(100),
Maths nvarchar(100),
Science nvarchar(100),
SocialScience nvarchar(100),
Total nvarchar(100),
Result nvarchar(100),
Rank nvarchar(100)
)

Insert Into @t
select '01','banu', 'English', '6thStd', 'A', 'Monthly', 'Apr', 2010, 77 ,77 ,87 ,67 ,66 ,374,'Pass',null Union all
Select '02','priya' ,'English','6thStd','A','Monthly','Apr', 2010, 56, 55 ,55 ,66 ,76 ,308 ,'Pass', null Union All
Select '03','c','English','6thStd','C','Monthly','Apr',2010 ,34 ,55 ,44 ,43 ,44 ,220 ,'Fail', null Union All
Select '04','b','English','6thStd','D','Quotally','Apr',2010 ,67 ,86 ,66 ,66 ,77 ,362 ,'Pass', null Union All
Select '08','ashok','English','6thStd','A','Monthly','Apr', 2010 ,55 ,45 ,33 ,55 ,44 ,232 ,'Fail', null
;with Rank(Id,StudentName,Total,Rank)
AS
(
Select admno,StudentName,Total,row_number() over(order by total desc)Rank from @t
Where Result='Pass'
)
select * from Rank




-- output
Id StudentName Total Rank
---------- -------------------- -------------------- --------------------
01 banu 374 1
04 b 362 2
02 priya 308 3


Author: vidhya             
Posted Date: November 10, 2010     Points: 5   

'row_number' is not a recognized function name.this error was accur............


i use this coding in this way:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
str = "Select Admno,Total,row_number() over(order by Total desc)Rank from marklistvix Where Result='Pass'"
da = New Data.SqlClient.SqlDataAdapter(str, con)
ds = New Data.DataSet
da.Fill(ds, "marklistvix")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "marklistvix"

end sub
plz rectify my error,how will i use this coding......

Author: Suresh             
Posted Date: November 10, 2010     Points: 5   

Try LIke this

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
 str = ";with Rank(Id,Total,Rank) " & _
" AS" & _
"(" & _
" Select admno,Total,row_number() over(order by total desc)Rank from @marklistvix " & _
"Where Result='Pass' " & _
")"

da = New Data.SqlClient.SqlDataAdapter(str, con)
ds = New Data.DataSet
da.Fill(ds, "marklistvix")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "marklistvix"

Author: vidhya             
Posted Date: November 10, 2010     Points: 5   

str = ";with Rank(Id,Total,Rank) " & _ " AS" & _ "(" & _


what is this i dint understand ur coding

Author: Suresh             
Posted Date: November 10, 2010     Points: 5   

This is Common Table Expression Method in sql. Which is available above sql 2005 server.

Did u get result

Author: vidhya             
Posted Date: November 10, 2010     Points: 5   

i use microsoft sql server enterprise manager ,its not working plz give me another solutiont

Author: Suresh             
Posted Date: November 10, 2010     Points: 5   

Hello Vidhya,
Can you do one thing, Run this query in ur sql server, let me know the version of ur sql server

Select @@version

Kindly send result of above select result

Author: vidhya             
Posted Date: November 12, 2010     Points: 5   

Microsoft SQL Server 2000 - version



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend