Query - Find 3rd maximum mark from each Section

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query - Find 3rd maximum mark from each Section

Author

Topic

velpmk
Starting Member

2 Posts

Posted - 2007-04-26 : 08:01:30

Hai Friends,

I have a table with these fields.[ sno,name,section,mark]. i need a query for this condition:
In this table,

Three sections is there. [ section A,Section B,Section C]
More than 10 students in each section.

so i need,
1. Find 3rd maximum mark from each section.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 08:35:10

[code]-- Stage the marks
DECLARE @Stage TABLE (RecID INT IDENTITY, Section VARCHAR(20), Mark INT)

INSERT @Stage
SELECT Section,
Mark
FROM SourceTable
ORDER BY Section,
Mark DESC

-- Show the expected output
SELECT s.Section,
s.Mark
FROM @Stage AS s
INNER JOIN (
SELECT Section,
MIN(RecID) AS RecID
FROM @Stage
GROUP BY Section
) AS x ON x.Section = s.Section AND x.RecID + 2 = s.RecID
ORDER BY s.Section[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 08:37:25

Interview question?

Peter Larsson
Helsingborg, Sweden

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-26 : 08:40:23

declare @tt table (sid int, section varchar(10), sname varchar(50), mark int)
insert @tt

select 1, 'A', 'raju', 89 union
select 1, 'A', 'mohan', 78 union
select 1, 'A', 'biresh', 92 union
select 1, 'A', 'vikram', 45 union
select 1, 'B', 'raja', 89 union
select 1, 'B', 'vijay', 48 union
select 1, 'C', 'ragu', 79 union
select 1, 'C', 'visu', 59 union
select 1, 'C', 'mano', 100

select section, min(mark) from @tt a where mark in
(select top 3 mark from @tt where section = a.section order by mark desc) group by a.section

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-26 : 08:42:46

This is the default question in interview :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-26 : 08:52:39

quote:
Originally posted by pbguy

This is the default question in interview :-)

what happened to the 3rd highest salary question ?

velpmk
Starting Member

2 Posts

Posted - 2007-04-26 : 09:00:23

Thanks friend. i got the result using ur query.
and also if suppose two persons had same mark in third position, ur query get only the first person. i want to get these these two persons also.

Once again thanks ur help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-26 : 09:16:47

That is not what you asked for originally...

-- Stage the marks
DECLARE	@Stage TABLE (RecID INT IDENTITY, Section VARCHAR(20), Mark INT)

INSERT		@Stage
SELECT		Section,
		Mark
FROM		SourceTable
ORDER BY	Section,
		Mark DESC

-- Show the expected output
SELECT		s2.Section,
		s2.Mark
FROM		@Stage AS s1
INNER JOIN	(
			SELECT		Section,
					MIN(RecID) AS RecID
			FROM		@Stage
			GROUP BY	Section
		) AS x ON x.Section = s.Section AND x.RecID + 2 = s.RecID
INNER JOIN	@Stage AS s2 ON s2.Section = s1.Section AND s2.Mark = s1.Mark
ORDER BY	s2.Section

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources