select max from inner join

Other SQL Server Topics
1

Hi i have 2 tables with one to many relationship, header to rows . my query look like this

SELECT HEADER_ID, ROW_ID, COL1,COL2 … ,

(SELECT MAX(row) where HEADER_ID = HEADER_ID)

FROM HEADER INNER JOIN ROWS ON …. WHERE …

WHAT IS THE BEST WAY (IF NEEDED) TO CHANGE THE SELECT MAX TO INNER JOIN

FROM

what will be

2 

SELECT H.HEADER_ID, R.ROW_ID
FROM HEADER H
CROSS APPLY (
    SELECT MAX(row) AS MAX_ROW
    FROM ROWS R
    WHERE R.HEADER_ID = H.HEADER_ID
) AS R
3

hi

hope this helps

another way of doing this

SELECT h.HEADER_ID, r.ROW_ID, r.COL1, r.COL2
FROM HEADER h
JOIN (
    SELECT ROW_ID, HEADER_ID, COL1, COL2, SomeColumn,
           RANK() OVER (PARTITION BY HEADER_ID ORDER BY SomeColumn DESC) AS rk
    FROM ROWS
) r ON h.HEADER_ID = r.HEADER_ID
WHERE r.rk = 1