如何在SQL中获取每个组的最后一条记录 [英] How to get the last record per group in SQL

问题描述

我面临一个相当有趣的问题.我有一个具有以下结构的表:

I am facing a rather interesting problem. I have a table with the following structure:

CREATE TABLE [dbo].[Event]
(
    Id int IDENTITY(1,1) NOT NULL,
    ApplicationId nvarchar(32) NOT NULL,
    Name nvarchar(128) NOT NULL,
    Description nvarchar(256) NULL,
    Date nvarchar(16) NOT NULL,
    Time nvarchar(16) NOT NULL,
    EventType nvarchar(16) NOT NULL,
    CONSTRAINT Event_PK PRIMARY KEY CLUSTERED ( Id ) WITH (
        PAD_INDEX = OFF, 
        STATISTICS_NORECOMPUTE = OFF, 
        IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, 
        ALLOW_PAGE_LOCKS  = ON
    )
)

所以问题是我必须在网格中显示此数据.有两个要求.第一个是显示所有事件，而不管应用程序是什么事件.这很简单-select语句可以很轻松地完成工作.

So the issue is that I have to display this data in a grid. There are two requirements. The first one is to display all events regardless of what application threw them. This is simple - a select statement will do the job very easily.

第二个要求是能够按Application对事件进行分组.换句话说，显示所有事件的方式是，如果多次重复ApplicationId，则仅抓取每个应用程序的最后一个条目.此查询/视图中不再需要事件(Id)的主键.

The second requirement is to be able to group events by Application. In other words display all events in a way that if the ApplicationId is repeated more than once, grab only the last entry for every application. The primary key of the Event (Id) at this point is no longer needed in this query/view.

您可能还会注意到事件日期和时间是字符串格式.可以，因为它们遵循标准的日期时间格式:mm/dd/yyyy和hh:mm:ss.我可以按如下所示拉动它们:

You may also notice that the Event Date and Time are in string format. This is ok because they follow the standard date time formats: mm/dd/yyyy and hh:mm:ss. I can pull those as follows:

Convert( DateTime, (Date + ' ' +  Time)) AS 'TimeStamp'

我的问题是，如果我在其余的列上使用AGGREGATE函数，我将不知道它们的行为:

My issue is that if I use AGGREGATE functions on the rest of the columns I don't know how would they behave:

SELECT
    ApplicationId,
    MAX(Name),
    MAX(Description),
    MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp',
    MAX( EventType )
FROM
    Event
GROUP BY
    ApplicationId

我犹豫这样做的原因是因为像MAX这样的函数将从(子)记录集中返回给定列的最大值.不必拉最后一条记录！

The reason I am hesitant to do so is because a function such as MAX will return the largest value for a given column from a (sub)set of records. It does not necessary pull the last record!

关于如何在每个应用程序的基础上仅选择最后一条记录的任何想法吗?

Any ideas on how to select only the last record on a per application basis?

推荐答案

您可以使用常用表表达式. /p>

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

这篇关于如何在SQL中获取每个组的最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！