Sql Server DataBase
-
Hi I have a table Student like below SName Subject Marks Date A AAA 10 2011-01-01 B AAB 20 2012-04-01 B AAB 20 2012-03-01 I need sql query to find the 3rd highest marks for each subject. please do the needful Cloumname DATA Sname -- A, B, B Subject - AAA, AAB, AAB Marks -- 10,20,20 Date --2011-01-01,2012-04-01,2012-03-01
-
Hi I have a table Student like below SName Subject Marks Date A AAA 10 2011-01-01 B AAB 20 2012-04-01 B AAB 20 2012-03-01 I need sql query to find the 3rd highest marks for each subject. please do the needful Cloumname DATA Sname -- A, B, B Subject - AAA, AAB, AAB Marks -- 10,20,20 Date --2011-01-01,2012-04-01,2012-03-01
We are not going to do your homework for you, but will give you hints There are several ways you could do this - the classic way would be by using the TOP[^] predicate and subqueries, or using the ROW_NUMBER()[^] function, partitioned by subject and ordering by mark.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Hi I have a table Student like below SName Subject Marks Date A AAA 10 2011-01-01 B AAB 20 2012-04-01 B AAB 20 2012-03-01 I need sql query to find the 3rd highest marks for each subject. please do the needful Cloumname DATA Sname -- A, B, B Subject - AAA, AAB, AAB Marks -- 10,20,20 Date --2011-01-01,2012-04-01,2012-03-01
Try this
DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate DATE)
INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'SELECT X.*
FROM ( SELECT
t.*
,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3/*
SName Subject Marks ExamDate Rn
A Subject1 77 2011-01-01 3
B Subject2 70 2012-03-01 3*/
-
Try this
DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate DATE)
INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'SELECT X.*
FROM ( SELECT
t.*
,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3/*
SName Subject Marks ExamDate Rn
A Subject1 77 2011-01-01 3
B Subject2 70 2012-03-01 3*/
And I have a question in biology that needs a response for, can you
do the needfulfor that one as well. This will be pay back when the idjit ends up working with you!Never underestimate the power of human stupidity RAH
-
And I have a question in biology that needs a response for, can you
do the needfulfor that one as well. This will be pay back when the idjit ends up working with you!Never underestimate the power of human stupidity RAH
Respected Sir, Hope you are doing well. The question being asked can be solved in 2 ways, as per my understanding. #1: If the data storage format is like StudentRoll,Name,English,Physics,Biology,Maths etc. (which he didn't asked ) In this case, in-order to flatten the data, first we will use UNPIVOT . Once done, the next step will be to use Row_Number() and get the N-th highest marks. Finally , to get the original structure back, we should apply PIVOT. #2: If the storage format is like SName,Subject,Marks(which he asked ) In this case, since the data is already flattened by Subjects, we must apply the RANKING function (as I applied Row_Number()) here and get the N-th highest one. Kindly let me know, if my understanding of your question is correct in which case please provide the feedback of the response.
-
Respected Sir, Hope you are doing well. The question being asked can be solved in 2 ways, as per my understanding. #1: If the data storage format is like StudentRoll,Name,English,Physics,Biology,Maths etc. (which he didn't asked ) In this case, in-order to flatten the data, first we will use UNPIVOT . Once done, the next step will be to use Row_Number() and get the N-th highest marks. Finally , to get the original structure back, we should apply PIVOT. #2: If the storage format is like SName,Subject,Marks(which he asked ) In this case, since the data is already flattened by Subjects, we must apply the RANKING function (as I applied Row_Number()) here and get the N-th highest one. Kindly let me know, if my understanding of your question is correct in which case please provide the feedback of the response.
My apologies, I was being sarcastic. The point I was trying to make is that Chris had given him the pointers where he could research and learn how to do the job. Whereas you gave him the solution, which does not help him learn. If it was a difficult question then the example may have been the best way to answer. As it is an extremely basic question the OP needs to do some learning/research/school work rather than cut and paste your answer. Asking someone to "do the needful" is just plain rude.
Never underestimate the power of human stupidity RAH