如何在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
    )
)

所以问题是我必须在网格中显示这些数据.有两个要求.第一个是显示所有事件，不管是什么应用程序抛出它们.这很简单 - 一个选择语句可以很容易地完成这项工作.

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?

推荐答案

您可以使用 排名函数和公用表表达式.

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屋！