How to get the last record per group in SQL

by

You can use a ranking function and a common table expression.

WITH e AS
(
     SELECT *,
         ROW_NUMBER() OVER
         (
             PARTITION BY ApplicationId
             ORDER BY CONVERT(datetime, [Date], 101) DESC, [Time] DESC
         ) AS Recency
     FROM [Event]
)
SELECT *
FROM e
WHERE Recency = 1

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. Retrieving last record in each group from database – SQL Server 2005/2008
  4. SET versus SELECT when assigning variables?
  5. T-SQL: Export to new Excel file
  6. How to print VARCHAR(MAX) using Print Statement?
  7. Convert Xml to Table SQL Server
  8. Concatenate values based on ID
  9. SQL MERGE statement to update data
  10. Splitting delimited values in a SQL column into multiple rows
  11. Select a Column in SQL not in Group By
  12. T-SQL substring – separating first and last name
  13. How to get week number of the month from the date in sql server 2008
  14. SQL server select distinct rows using most recent value only
  15. Average of multiple columns
  16. T-SQL strip all non-alpha and non-numeric characters
  17. Avoiding IF ELSE due to variable been NULL
  18. Cumulative Total in MS Sql server [duplicate]
  19. What are row, page and table locks? And when they are acquired?
  20. Recursive Child/Parent queries in T/SQL
  21. Get first day of week in SQL Server
  22. sql query to return differences between two tables
  23. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  24. Check if a row exists, otherwise insert
  25. SQL Server equivalent to MySQL enum data type?
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  28. Using IF ELSE statement based on Count to execute different Insert statements
  29. Restrict SQL Server Login access to only one database
  30. How to query xml column in tsql

Leave a Comment