Как получить последнюю запись для каждой группы в SQL

Я столкнулся с довольно интересной проблемой. У меня есть таблица со следующей структурой:

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 сделает работу очень легко.

Второе требование состоит в том, чтобы иметь возможность группировать события на Application. Другими словами, отображать все события таким образом, что если ApplicationId повторяется более одного раза, хватайте только последнюю запись для каждого приложения. Первичный ключ Event (Id) в этой точке больше не нужен в этом запросе/представлении.

Вы также можете заметить, что дата и время события находятся в строчном формате. Это нормально, потому что они соответствуют стандартным форматам даты: mm/dd/yyyy и hh: mm: ss. Я могу вытащить их следующим образом:

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

Моя проблема в том, что если я использую функции AGGREGATE для остальных столбцов, я не знаю, как они будут себя вести:

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

Причина, по которой я не решаюсь это сделать, состоит в том, что функция, такая как MAX, вернет наибольшее значение для данного столбца из (под) набора записей. Не нужно тянуть последнюю запись!

Любые идеи о том, как выбрать только последнюю запись на основе каждого приложения?

Ответы

Ответ 1

Вы можете использовать функцию и общее табличное выражение.

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

Ответ 2

С SQL Server 2012 вы можете просто

SELECT 
    [Month]
    , [First] = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month])
    , [Last]  = FIRST_VALUE(SUM([Clicks])) OVER (ORDER BY [Month] DESC)
FROM 
    [dbo].[Table]
GROUP BY [Month]
ORDER BY [Month]

Ответ 3

SELECT
    E.ApplicationId,
    E.Name,
    E.Description,
    CONVERT(DateTime, (E.Date + ' ' + E.Time)) AS 'TimeStamp',
    E.EventType
FROM
    Event E
    JOIN (SELECT ApplicationId,
                 MAX(CONVERT(DateTime, (Date + ' ' + Time))) AS max_date
            FROM Event
        GROUP BY ApplicationId) EM 
      on EM.ApplicationId = E.ApplicationId
     and EM.max_date = CONVERT(DateTime, (E.Date + ' ' + E.Time)))

Ответ 4

Поскольку у вас нет предложения where, подмножество записей, это все записи. Но вы помещаете max на неправильные столбцы, я думаю. Этот запрос даст вам то, что вы ищете.

Select max(applicationid), name, description, CONVERT(DateTime, (Date + ' ' + Time)) 
from event
group by name, description, CONVERT(DateTime, (Date + ' ' + Time))

Ответ 5

Для этого вы можете использовать таблицу подкатегорий или CTE:

;WITH CTE_LatestEvents as (
SELECT
    ApplicationId,    
    MAX( CONVERT(DateTime, (Date + ' ' + Time))) AS 'LatestTimeStamp',
FROM
    Event
GROUP BY
    ApplicationId
)
SELECT
    ApplicationId,
    Name,
    Description,
    CONVERT(DateTime, (Date + ' ' + Time))) AS 'TimeStamp',
    EventType
FROM
    Event e
    Join CTE_LatestEvents le 
        on e.applicationid = le.applicationid
        and CONVERT(DateTime, (e.Date + ' ' + e.Time))) = le.LatestTimeStamp

Ответ 6

Вы можете использовать вспомогательный запрос с группой: группа по аргументу не обязательно должна быть в списке. Это предполагает, что Id является автоматическим приращением, так что самый большой из них является самым последним.

SELECT
    ApplicationId,
    Name,
    Description,
    CONVERT(DateTime, (Date + ' ' + Time)) AS 'TimeStamp',
    EventType
FROM
    Event e
WHERE
    Id in (select max(Id) from Event GROUP BY ApplicationId)

Ответ 7

Я думаю, что это будет работать для многих, желающих получить последнюю вставленную запись, и она должна быть группой:

выберите * from (выберите * из TableName ORDER BY id DESC) AS x GROUP BY FieldName

Он будет работать для следующего:

Структура таблицы Статус имени ID 1 Джунайд Да 2 Jawad Нет 3 Фахад Да 4 Junaid No 5 Кашиф Да

Результаты после запроса Статус имени ID 4 Junaid No 2 Jawad Нет 3 Фахад Да 4 Кашиф Да

Это просто результат последней записи группы по именам.

Ответ 8

Через 6 лет появится еще один ответ для SQL Server:

select t1.[Id], t2.[Value]  
from [dbo].[Table] t1  
  outer apply (  
    select top 1 [Value]  
      from [dbo].[Table] t2  
        where t2.[Month]=t1.[Month]  
      order by [dbo].[Date] desc  
  )

Хотя мне нравится решение Postgresql гораздо лучше с его отличной функцией, которая удобнее вводить и намного эффективнее:

select distinct on (id),val  
from tbl  
order by id,val