asked
Display 1 row only per company
Select query returns data like this: ID COMPANY DATE TYPE SUBJECT 1 Comp1 24/03/2017 Email Some text 2 Comp1 07/04/2017 Mailing Some other text 3 Comp2 16/04/2017 Note Text I need to show only the most recent communication for each company, so in this case the second and third rows. Date/Type/Subject sit in one table, Company in another. Using Max for the data doesn't work because Type and Subject (which also need to be displayed) are different.
You could try: WITH myCTE AS ( SELECT ID, Company, Date, Type, Subject, ROW_NUMBER() OVER (PARTITION BY COMPANY ORDER BY DATE DESC) AS RowNum FROM MyTable) SELECT ID, Company, Date, Type, Subject FROM myCTE WHERE RowNum = 1 Something like that, anyway. Untested. References: * CTE - [Common Table Expression][1] * [ROW_NUMBER][2] [1]:
https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql [2]:
https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
17 People are following this question.
