Select nth highest marks
-
Could anybody help me out in finding 2nd highest marks from a department ?
Lets assume we have a table structure as follows.
DeptId Marks
D1 80
D2 60
D1 100
D2 80
D3 56
So my output should be
D1 80
D2 60
D3 56
I tried in some other way .. i do not want lengthy query, but i know it can be done.. Let me know
Sree
-
What if there's a tie at the nth place ?
If there were 2 records for D1 with a mark of 80, what are your requirements ?
-
As it seems to me, as long as they only require the marks, without any other specification of the case, it makes no difference if there is a tie precisely at the Nth place. However, there is difference if ties occur on places below N.
Example: In a set of 125, 100, 100, 75, 50, 44 second highest is always 100 (we just can have different opinions on which of the two 100 it is) - but what is the 4th highest value? 75 or 50?
-
Hi!!!
I agree with VLADAN......
here is ur solution..
SELECT * FROM TABLE1
WHERE MARKS NOT IN (SELECT MAX(MARKS) FROM TABLE1)
UNION
SELECT E.ID,E.MARKS FROM TABLE1 E INNER JOIN (SELECT ID,MAX(MARKS) MARKS FROM TABLE1 GROUP BY ID) P
ON E.ID = P.ID
WHERE E.MARKS NOT IN (SELECT MAX(MARKS) FROM TABLE1) ORDER BY 2 DESC
Regards,
Papillon
-
SELECT a.DeptId, MIN(a.Marks) AS [Marks]
FROM
a
WHERE a.Marks IN
(SELECT TOP 2 b.Marks FROM
b WHERE b.DeptId = a.DeptId ORDER BY Marks DESC)
GROUP BY a.DeptId
ORDER BY a.DeptId
Far away is close at hand in the images of elsewhere.
Anon. -
Well David reply seems to be more promising to me as i can vary top 2 to any level. Say for nth highest also. As far as shashank's reply concerned, should we need to have another union to have another level ?
Thanks David and Shashank. I appreciate it.
Sreenath
-
Sreenath,
your first post is a bit inconsistent. You want to select the 2nd highest value, but for D3 there is only 1 value. That means, D3 should not appear in your result at all... or, if it appears, you have to redefine what the result should show. I'm acting on the assumption that D3 should not appear in the result.
If you want to have variability, there is a little problem with the solution you liked - you can't pass a parameter into the TOP clause... so you'd have to use dynamic SQL, which is better to avoid if static SQL can do the job. Unfortunately you didn't specify what to do with possible ties, so I'll post some SQL and you'll have to modify it if necessary. I am eliminating ties in a way that seems to be sensible to me, but your needs can differ. Generally both solutions are based on "tell me how many marks are greater than this one for every value" and then select a value according to the parameter.
create table #mytable(DeptId varchar(5), Marks int)
insert into #mytable values ('D1',80)
insert into #mytable values ('D2',60)
insert into #mytable values ('D1',100)
insert into #mytable values ('D2',80)
insert into #mytable values ('D3',56)
insert into #mytable values ('D1',80)
insert into #mytable values ('D1',60)
insert into #mytable values ('D1',50)
DECLARE @parameter int
SET @parameter = 2 /*2nd highest*/
/*method 1 - with subselect*/
SELECT DISTINCT Q.deptid, Q.marks, rank
FROM
(select DeptId, Marks, 1+(select count(distinct marks) from #mytable where deptid = m.deptid and marks > m.marks) as rank
from #mytable m) Q
WHERE Q.rank = @parameter
/*method 2 - with JOIN*/
SELECT a.deptid, a.marks, count(distinct b.marks)
FROM #mytable a
JOIN #mytable b ON b.deptid = a.deptid AND a.marks <= b.marks
GROUP BY a.deptid, a.marks
HAVING count(distinct b.marks) = @parameter
-
Nice solutions Vladen
you can't pass a parameter into the TOP clause... Not until SQL2005
you didn't specify what to do with possible ties Just for my own curiosity, my query altered to eliminate ties
SELECT a.DeptId, MIN(a.Marks) AS [Marks]
FROM
a
WHERE a.Marks IN
(SELECT TOP 2 b.Marks FROM
b WHERE b.DeptId = a.DeptId GROUP BY b.Marks ORDER BY b.Marks DESC)
GROUP BY a.DeptId
ORDER BY a.DeptId
but for D3 there is only 1 value. That means, D3 should not appear in your result at all... True, again my solution, altered again...
SELECT a.DeptId, MIN(a.Marks) AS [Marks]
FROM
a
WHERE a.Marks IN
(SELECT TOP 2 b.Marks FROM
b WHERE b.DeptId = a.DeptId GROUP BY b.Marks ORDER BY b.Marks DESC)
GROUP BY a.DeptId
HAVING COUNT(*) > 1
ORDER BY a.DeptId
Far away is close at hand in the images of elsewhere.
Anon. -
Vladen,
Right. Your solution fits the bill with a question,
With David solution gives me an idea what if the department does not have any second highest marks ? isnt first highest marks holds good for any level ? So isnt he right ?
Sreenath
-
Well, that's something you have to know (or find out) - I don't know who will use the result and for what it will be used. But... let's analyze it. If you place 3 orders and then try to find out, what was the value of your 4th order, do you want to be told that there wasn't any 4th order, or do you want to get the value of your 3rd order? In this case I think the first is right. So far, I don't see any significant difference between this and your problem. Please try to explain why first highest marks should be used if you want to know second highest ... wouldn't that be rather confusing?
However, question remains what with the ties? To use the above example, if you place 3 orders that all have the same value, I'm not sure whether there is or isn't a 3rd highest order... There are three orders, but they all will be treated as first highest. So again, to get anywhere we need to have YOUR DEFINITION of what should the code return in every possible combination of circumstances. If athlete's best time for 100 meter is 10 seconds and he achieved it in 3 different races, is it also his second and third best time? I wouldn't say so, though opinions can differ here.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply