need help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

need help

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2009-07-20 : 07:54:23

i have three tables like

student
-------
stid
stname

exam
-------
exid,
exname

examdet
------
exdetid,
stid,
exid,
marks

how to list down exam wise topper with the marks obtained

stname,
exname
marks

Thanks

Zakeer Sk

SkotCollinz
Starting Member

8 Posts

Posted - 2009-07-20 : 08:05:35

If i've understodd you correctly...

select a.stname, b.exname, c.marks
from examdet c
inner join student a on c.stid = a.stid
inner join exam b on c.exid = b.exid
order by c.marks desc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 23:58:27

[code]
DECLARE @EXAMS TABLE
(
EXAMID int,
EXAMNAME varchar(15)
)
INSERT INTO @EXAMS
SELECT 1, 'ENGLISH' UNION ALL
SELECT 2, 'MATHEMATICS' UNION ALL
SELECT 3, 'PHYSICS' UNION ALL
SELECT 4, 'FRENCH'

DECLARE @STUDENT TABLE
(
STID int,
NAME varchar(5)
)
INSERT INTO @STUDENT
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 3, 'C' UNION ALL
SELECT 4, 'D'

DECLARE @EXAMDET TABLE
(
EXAMDETID int,
STID int,
EXAMID int,
MARKS int
)

INSERT INTO @EXAMDET
SELECT 100, 1, 1, 80 UNION ALL
SELECT 101, 2, 1, 80 UNION ALL
SELECT 102, 3, 1, 80 UNION ALL
SELECT 103, 1, 2, 70 UNION ALL
SELECT 104, 2, 2, 70 UNION ALL
SELECT 105, 3, 2, 75

SELECT STUDENTNAME, EXAMNAME, MARKS
FROM
(
SELECT STUDENTNAME = S.NAME,
EXAMNAME = E.EXAMNAME,
MARKS = D.MARKS,
RANK = DENSE_RANK() OVER (PARTITION BY D.EXAMID ORDER BY D.MARKS DESC)
FROM @EXAMDET D
INNER JOIN @STUDENT S ON D.STID = S.STID
INNER JOIN @EXAMS E ON D.EXAMID = E.EXAMID
) M
WHERE M.RANK = 1

/*
STUDENTNAME EXAMNAME MARKS
----------- --------------- -----------
A ENGLISH 80
B ENGLISH 80
C ENGLISH 80
C MATHEMATICS 75

(4 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 00:00:40


SELECT	STUDENTNAME	= S.NAME,
	EXAMNAME	= E.EXAMNAME,
	MARKS		= D.MARKS,
	RANK		= DENSE_RANK() OVER (PARTITION BY D.EXAMID ORDER BY D.MARKS DESC)
FROM	@EXAMDET D
	INNER JOIN
	(
		SELECT	EXAMID, MARKS = MAX(MARKS)
		FROM	@EXAMDET
		GROUP BY EXAMID, EXAMID
	) M			ON	D.EXAMID	= M.EXAMID
				AND	D.MARKS		= M.MARKS
	INNER JOIN @STUDENT S	ON	D.STID		= S.STID
	INNER JOIN @EXAMS E	ON	D.EXAMID	= E.EXAMID

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources