CODEWITHSUNDEEP
sql-serversql-server-2008joingroup-by
Nizam Muhammed
Nizam Muhammed

Reputation: 21

How make possible Joins with group by clause

I have two tables, table1 has 2 columns as

id     name
1      Amal
2      Varun
3      Sari

table2 has 3 columns as

id      Subject     marks
1       Maths       80
1       Malayalam   75
1       History     45
2       Maths       90
2       Malayalam   85
2       History     50
3       Maths       88
3       Malayalam   75
3       History     80

My question is to find the names who has the maximum mark for each subject (Subject wisw topper) the resultant table have to includes the fields name subject and marks

I tested with the following query

SELECT 
    table1.Student_Name, (table2.subject), max(table2.Marks_obt) 
FROM 
    table2
INNER JOIN
    table1 ON table2.stud_id = table1.Student_ID
GROUP BY 
   [Student_Name], table2.Subject 
HAVING
   MAX(Marks_obt) IN (SELECT MAX(Marks_obt) AS total_marks
                      FROM table2
                      GROUP BY subject)

In SQL Server 2008, but I got the result as 

name    subject   
Sari    History     80
Varun   Malayalam   85
Amal    Maths       80
Varun   Maths       90

how I get the topper of three subject with these manner?

Upvotes: 0

Views: 73

Answers (5)

Esperento57
Esperento57

Reputation: 17492

other solution with imbication:

   with maxi as (
   select Subject, max(marks) maximark from table2
   group by Subject
   )
   select (select top 1 f2.name from table1 f2 inner join table2 f3 on f2.id=f3.id where f1.maximark=f3.marks and f1.Subject=f3.Subject) as Name, f1.* 
   from maxi f1

Upvotes: 0

Esperento57
Esperento57

Reputation: 17492

you can use a cross apply too like this

   with maxi as (
   select Subject, max(marks) maximark from table2
   group by Subject
   )
   select * from maxi f1
   cross apply
   (
   select top 1 f2.name from table1 f2 inner join table2 f3 on f2.id=f3.id
   where f1.maximark=f3.marks and f1.subject=f3.subject   
   ) f3

if multiple users are possible for a maxi mark, remove "top 1"

Upvotes: 0

Guruprakash Chinnasamy
Guruprakash Chinnasamy

Reputation: 144

CREATE TABLE #table1 
(Student_ID INT,
Student_Name VARCHAR(20))

INSERT INTO #table1
SELECT 1,'Amal'
UNION 
SELECT  2,'Varun'
UNION
SELECT 3,'Sari'


CREATE TABLE #table2
(
stud_id INT,
[subject] VARCHAR(20),
Marks_obt INT
)

INSERT INTO #table2
SELECT 1,'Maths',80
UNION
SELECT 1,'Malayalam',75
UNION
SELECT 1,'History',45
UNION
SELECT 2,'Maths',90
UNION
SELECT 2,'Malayalam',85
UNION
SELECT 2,'History',80
UNION
SELECT 3,'Maths',88
UNION
SELECT 3,'Malayalam',75
UNION
SELECT 3,'History',80   

/*Table 1*/
SELECT * FROM  #table1
/*Table 2*/
SELECT * FROM #table2


/*Top Mark*/
SELECT [subject],
   Student_Name,
   Marks_obt
FROM(SELECT Student_Name,
           [subject],
           Marks_obt,
           RANK()
             OVER(
               PARTITION BY [subject]
               ORDER BY Marks_obt DESC) RowNum
    FROM   #table1 T1
           JOIN #table2 T2
             ON T1.Student_ID= T2.stud_id) AS data
WHERE  data.RowNum = 1 


DROP TABLE #table1,#table2

Upvotes: 0

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use Rank You will not miss any people for example like two people will get same highest marks in same subject. And if you want query to find 2nd highest marks or 3rd highest use Dense_Rank() function even dense_rank() also works for finding 1st highest. For More cilck here 

SELECT NAME,
       SUBJECT,
       MARKS
FROM   (SELECT NAME,
               SUBJECT,
               MARKS,
               rank()
                 OVER(
                   PARTITION BY [SUBJECT]
                   ORDER BY MARKS DESC) RNO
        FROM   #TABLE1 T
               JOIN #TABLE2 T2
                 ON T.ID = T2.ID) A
WHERE  RNO = 1

Upvotes: 0

sagi
sagi

Reputation: 40491

You can use ROW_NUMBER() :

SELECT s.subject,s.name,s.marks
FROM(
    SELECT t1.*,t2.subject,t2.marks,
           ROW_NUMBER() OVER(PARTITION BY t2.subject ORDER BY t2.marks DESC) as rnk
    FROM Table1
    JOIN Table2
     ON table2.stud_id = table1.Student_ID) s
WHERE s.rnk = 1

Upvotes: 1

Related Questions