CODEWITHSUNDEEP
sql-serverjoin
Ajit Kumar
Ajit Kumar

Reputation: 75

Join with aggregate

SQL Server Interview Question

I faced a question in interview like this. The output will contain the each ID highest age with highest mark.

Upvotes: 0

Views: 33

Answers (2)

Muhammad Azim
Muhammad Azim

Reputation: 329

SELECT A.ID,A.Name,MA.MaxAge Age,MM.MaxMarks Marks FROM tbl2 A
LEFT JOIN (SELECT Id, Max(Age)MaxAge FROM tbl1 GROUP BY ID) MA ON MA.ID = a.ID
LEFT JOIN (SELECT Id, Max(Marks)MaxMarks FROM tbl1 GROUP BY ID) MM ON MM.ID = a.ID

Use this query, you will get your result

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14699

Use ROW_NUMBER() WITH PARTITION BY as below to get your output

;WITH T AS
(
    SELECT
        ID,
        Age,
        Marks,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Age DESC,MArks DESC) AS PartNo
    FROM @tbl1
)
SELECT
    T.ID,
    T2.Name,
    T.Age,
    T.Marks
FROM T
LEFT JOIN @tbl2 T2 ON T.ID=T2.ID
WHERE PartNo=1

Upvotes: 1

Related Questions