Search code examples
sqlt-sqlsql-server-2014

Group by in an aggregate function

On SQL Server 2014, I have a table visits(id_visit, date, id_person) I cant figure out a way of getting the id_ of the last visit for each person in an aggregate function

don't really know if it can help but here is kind of what i'd like to have

select *
from visits
where id_visit in (select id_visit, max(date) from visits group by id_person)

If i had like visits (id_visit, id_person, date) 1,1,'2017-07-17' 2,1,'2017-01-01' 3,2,'2016-01-01' 4,2,'2015-01-01' 5,2,'2013-01-01' i'd like it to return 1,3,5

Solution

  • You could use ROW_NUMBER:

    WITH cte AS (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY id_person ORDER BY date DESC) AS rn
  FROM visits
)
SELECT *
FROM cte
WHERE rn = 1;

    It will return last visit per person.