Sql server find 2 rank

Question

0.00/5 (No votes)

See more:

Hi , this my table student how to find 2 rank of this table
can you write query for this table without total how to find rank

studId studName mark1  mark2   mark3
S001	Raja	60	70        40
S002	Siva	40  	79        77
S003	Kumar	80	30        90
S004	Mani	60	70        40
-------------------------------------

Posted 31-Aug-12 19:22pm

manuel_joseph

Updated 31-Aug-12 20:21pm

Wendelius

v2

Add a Solution

4 solutions

Solution 2

if you don't wanna insert a sum into table then.. made it in query itself..
using below query you will get 2nd rank students details without store a sum in your table..

SQL

select * from exam where (mark1+mark2+mark3) = select max(mark1+mark2+mark3) test From eaxm where (mark1+mark2+mark3)  not in (select max(mark1+mark2+mark3)  test From exam)

Posted 31-Aug-12 19:46pm

ssd_coolguy

v2

Solution 4

I am giving you max 2 highest salary example

Select max(sal)
from Emp
where sal not in (Select top 1 sal from Emp)

Posted 31-Aug-12 20:51pm

Kashinath Patil

Solution 1

StudentID	Name	Mark1	Mark2	Mark3	Sum
S001		Raju	60	70	40	170
S002		Siva	40	79	77	196
S003		Kumar	80	30	90	200
S004		Mani	60	70	40	170

SQL

SELECT MAX(Sum1) FROM Exam WHERE sum1 NOT IN (SELECT MAX(sum1) FROM Exam)

Posted 31-Aug-12 19:27pm

Abhai Oza

Updated 31-Aug-12 20:22pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Arul R Ece · Accepted Answer · 2012-08-31T19:55:00

Solution 3

HI ,,,,

Please try the below query

SQL

with cte_ex as 
(select distinct studId,studName,(mark1+mark2+mark3) as sumt,ROW_NUMBER() over (order by (select 1 ))as rowno from tmpstd)
select  A.studId,A.studName,A.sumt  from cte_ex A
join cte_ex B on A.rowno=B.rowno 
where A.rowno=2

Posted 31-Aug-12 19:55pm

Arul R Ece

Updated 31-Aug-12 20:21pm

v2