Student who got MAX marks in subject -dba.se

-- http://dba.stackexchange.com/a/116637/8783
-- Author : KIN Shah
-- http://dba.stackexchange.com/users/8783/kin

USE [your_test_db_name]
GO

/****** Object:  Table [dbo].[Marks]    Script Date: 9/30/2015 1:11:14 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Marks](
    [stud_id] [INT] IDENTITY(1,1) NOT NULL,
    [stud_name] [VARCHAR](50) NULL,
 CONSTRAINT [PK_Marks] PRIMARY KEY CLUSTERED
(
    [stud_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [master]
GO

/****** Object:  Table [dbo].[student]    Script Date: 9/30/2015 1:11:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[student](
    [stud_id] [INT] IDENTITY(1,1) NOT NULL,
    [subject] [VARCHAR](30) NULL,
    [marks] [INT] NULL,
 CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED
(
    [stud_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[student]  WITH CHECK ADD  CONSTRAINT [FK_student_Marks] FOREIGN KEY([stud_id])
REFERENCES [dbo].[Marks] ([stud_id])
GO

ALTER TABLE [dbo].[student] CHECK CONSTRAINT [FK_student_Marks]
GO



DECLARE @n INT

SET @n = ABS(CHECKSUM(NewId())) % 14
INSERT INTO dbo.Marks
SELECT 'chetan'+CAST( @n AS VARCHAR(3))
UNION ALL
SELECT 'sanjay'+CAST(@n*2 AS VARCHAR(3))
UNION ALL
SELECT 'Mark'+CAST(@n*3 AS VARCHAR(3))
UNION ALL
SELECT 'Jose'+CAST(@n*4 AS VARCHAR(3))

GO 100000

INSERT INTO dbo.student
SELECT 'Maths', ABS(CHECKSUM(NewId())) % 14*1
UNION ALL
SELECT 'Maths', ABS(CHECKSUM(NewId())) % 14*2
UNION ALL
SELECT 'Science', ABS(CHECKSUM(NewId())) % 14*3
UNION ALL
SELECT 'Science', ABS(CHECKSUM(NewId())) % 14*4
UNION ALL
SELECT 'geography', ABS(CHECKSUM(NewId())) % 14*5
UNION ALL
SELECT 'geography', ABS(CHECKSUM(NewId())) % 14*6
GO 100000

-- run above multiple times
SELECT * FROM dbo.Marks     -- 441036
SELECT * FROM dbo.student   -- 440434 records
/*
delete from dbo.student
where stud_id not in (select stud_id from dbo.Marks)

*/


dbcc dropcleanbuffers
dbcc freeproccache
SET statistics TIME, IO ON
SELECT     stud_name AS [Student Name]
        , [subject] AS [Subject]
        , marks     AS [MAX Marks]
    FROM dbo.Marks m
 CROSS apply (SELECT   s.stud_id
                     ,
                     s.[subject]
                     , s.marks
                     , dense_rank() OVER (PARTITION BY s.[subject]  ORDER BY s.marks DESC) AS drnSub
              FROM dbo.student s
            ) s
                 WHERE s.stud_id = m.stud_id
                     AND s.drnSub  = 1
                 OPTION (maxdop 1)

    SET statistics TIME, IO OFF