MAX on group возвращает несколько значений с одинаковой датой, но разным временем

Question

Я следовал многим отличным советам на этом сайте по выбору МАКСА из группы строк.

У меня есть файл истории, и я хочу только верхнюю дату и комментарии для каждого номера проекта. Я создаю производную таблицу во вселенной Boxi на основе этой информации. Все идет очень хорошо, но если есть две записи для одного дня, но с разным временем, они оба возвращаются. Это дублирует эту запись в последующем отчете. Есть ли способ заставить команду MAX опуститься до уровня времени поля даты?

База данных SQL Server 2005

------------- Sql используется для производной таблицы

Select
   Projectno, Comment, CreatedOn
from 
   ReportHistory
Where
   ReportHistory.ItemName=('ProjectCode1')
and
   CreatedOn in(Select max(CreatedOn) FROM ReportHistory group by Projectno)

------------------- Пример базы данных

Projectno     Comment             Created on

1             Started             2013-01-04 11:04:00
2             Late                2013-01-06 11:22:00
3             Late                2013-01-07 11:06:00
1             On Time             2013-01-08 11:01:00   *these two both get selected*
1             Late                2013-01-08 12:05:00   *these two both get selected*
3             Back on schedule    2013-01-08 14:20:00
2             Still overdue       2013-01-09 09:01:00

Answer 1

MAX на DATETIME Тип данных, безусловно, учитывает время, но это не то, что не так с вашим запросом. Проблема в том, что вы не гарантируете, что максимальное значение для CreatedOn для правильного ProjectNo, Вы можете использовать аналитические функции для этого:

;WITH CTE AS
(
    SELECT  Projectno, 
            Comment, 
            CreatedOn,
            ROW_NUMBER() OVER(PARTITION BY ProjectNo ORDER BY CreatedOn DESC) RN
    FROM ReportHistory
    WHERE ReportHistory.ItemName = 'ProjectCode1'
)
SELECT Projectno, Comment, CreatedOn
FROM CTE
WHERE RN = 1

Answer 2

Я думаю, что вы получаете копии, когда даты в разных проектах совпадают.

Например, добавить в свои данные (4, "В срок", "2013-01-08 11:01:00") Тогда результат будет SQLFiddle

Но вам нужен этот результат SQLFiddle

SELECT *
FROM ReportHistory t
WHERE t.ItemName=('ProjectCode1')
  AND EXISTS (
              SELECT 1
              FROM ReportHistory                              
              WHERE projectNo = t.projectNo
              GROUP BY projectNo
              HAVING MAX(CreatedOn) = t.CreatedOn
              )

Answer 3

Запросить, если нет одинакового проекта с одинаковой датой:

SQLFIDDLEExample

SELECT h.Projectno,
       h.Comment,
       h.[Created on]
FROM ReportHistory h
WHERE h.[Created on] =(Select max(h2.[Created on])
                       FROM ReportHistory h2 
                       WHERE h2.Projectno = h.Projectno )
ORDER BY h.Projectno

Результат:

| PROJECTNO |          COMMENT |                     CREATED ON |
-----------------------------------------------------------------
|         1 |             Late | January, 08 2013 12:05:00+0000 |
|         2 |    Still overdue | January, 09 2013 09:01:00+0000 |
|         3 | Back on schedule | January, 08 2013 14:20:00+0000 |

Запросить, если есть одно и то же проектно с одинаковой датой:

SELECT h.Projectno,
       MAX(h.Comment) AS Comment,
       h.[Created on]
FROM ReportHistory h
WHERE h.[Created on] =(Select max(h2.[Created on])
                       FROM ReportHistory h2 
                       WHERE h2.Projectno = h.Projectno )
GROUP BY h.Projectno,
       h.[Created on]
ORDER BY h.Projectno