J Lyne J Lyne - 1 month ago 4
SQL Question

Two different methods of obtaining max row

The first statement is how I have needed to pull a min row based on the org's needs I work for. At first, I would

MIN(DATEFIELD)
but if someone has two entries on the same day, we had problems. Next I tried
MIN(OP__DOCID)
where OP__DOCID is the table's unique key. Problem here is if someone ever back-dated an entry they forgot to create, the results would be inaccurate. So, I came up with the below statement. It ensures I get the most recent result from each unique admission.

SELECT OP__DocID
FROM FD__CNSLG_BASIS24 AS PC1
WHERE (OP__DOCID =
(SELECT TOP(1)OP__DocID
FROM FD__CNSLG_BASIS24 AS PC2
WHERE PC2.ClientKey = PC1.Clientkey and PC2.ProgramAdmitKey = PC1.Programadmitkey
ORDER BY Date_Screening
)
)


Recently, I have learned about
OVER(PARTITION BY)
and have been curious as to the subtle differences in how it works v.s. the statement above, because I do get different result.

SELECT OP__DocID = Min(OP__DOCID) OVER (Partition BY Clientkey, Programadmitkey)
FROM FD__CNSLG_BASIS24


Any insight, or links to other pages I could read would be extremely helpful.

Thanks!

Answer

Just use window functions:

select pc.*
from (select pc.*,
             row_number() over (partition by Clientkey, ProgramAdmitKey
                                order by Date_Screening  -- do you mean DESC?
                               ) as seqnum
      from FD__CNSLG_BASIS24 PC
     ) pc
where seqnum = 1;

Note: this gets the first record based on the screening date. You might want DESC to get the most recent.

Comments