Find Max ContID

1

Hi,

I have the following query;

SELECT     TOP (100) PERCENT gprdsql.TblContact.prac_no, gprdsql.TblContact.cont_name, gprdsql.TblPracDetails.prac_status, gprdsql.TblContact.email, 
                      MAX(gprdsql.TblContact.cont_id) AS ContID
FROM         gprdsql.TblPracDetails INNER JOIN
                      gprdsql.TblContact ON gprdsql.TblPracDetails.prac_no = gprdsql.TblContact.prac_no
WHERE     (gprdsql.TblPracDetails.prac_status IN ('Active', 'On Hold%')) AND (gprdsql.TblContact.contact_type = 'Collections')
GROUP BY gprdsql.TblContact.prac_no, gprdsql.TblContact.cont_name, gprdsql.TblPracDetails.prac_status, gprdsql.TblContact.email, gprdsql.TblContact.email
ORDER BY gprdsql.TblContact.prac_no

Results;

prac_no       cont_name            prac_status                 email              contID
30               Ali                   Active                  ali@gmail.com            560
30               Jon                 Active                   jon@gmail.com           980
30               Jon                 Active                   jon@gmail.com            12
42               John               Active                   john@gmail.com         1000
88               Richard           Active                   richard@gmail.com       900
88               Rabah              Active                    rabah@gmail.com         910

How I can write a select statement from the above query to get the results as below

prac_no       cont_name            prac_status                 email             contID
30               Jon               Active                   jon@gmail.com        980
42               John             Active                   john@gmail.com        1000
88               Rabah            Active                    rabah@gmail.com      910

Thanks

2 
SELECT TOP (100) PERCENT prac_no, cont_name, prac_status, email, ContID
FROM (
    SELECT     pd.prac_no, c.cont_name, pd.prac_status, c.email, c.cont_id AS ContID,
                          ROW_NUMBER() OVER(PARTITION BY pd.prac_no ORDER BY c.cont_id DESC) AS row_num
    FROM         gprdsql.TblPracDetails pd INNER JOIN
                          gprdsql.TblContact c ON pd.prac_no = c.prac_no
    WHERE     (pd.prac_status IN ('Active', 'On Hold%')) AND (c.contact_type = 'Collections')
) AS derived
WHERE row_num = 1