nmess88 nmess88 - 4 months ago 10
SQL Question

How to select the latter row in SQL

I have a result set that looks like this:

enter image description here

As you can see some of the

contactID
are repeated with same
QuestionResponse
. And there is one with a different
QuestionResponse
(the one with red lines).

I want to group this by
ContactID
, but select the latter row. Eg: In case of
ContactID = 78100299
, I want to select the row with
CreateDate = 17:00:44.907
(or rowNum = 2).

I have tried this:

select
ContactID,
max(QuestionResponse) as QuestionResponse,
max(CreateDate) as CreateDate
from
theResultSet
group by
ContactID


This will NOT work because there could be
QuestionResponse
2 and then 1 for the same
contactID
. In that case the latter one will be the one with response 1 not 2.

Thank you for you help.

Answer

I would use ROW_NUMBER() that way:

WITH Query AS
(
    SELECT rowNum, ContactID, QuestionResponse, CreateDate,
    ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY CreateDate DESC) Ordered
    FROM theResultSet
)
SELECT * FROM Query WHERE Ordered=1
  1. Assign numbers in ContactID group by date, descending
  2. Filter results having number <> 1