Выберите колонку в SQL, а не в группе по

Я пытаюсь найти некоторую информацию о том, как выбрать неагрегатный столбец, который не содержится в операторе Group By в SQL, но ничего, что я нашел до сих пор, похоже, не отвечает на мой вопрос. У меня есть таблица с тремя столбцами, которые я хочу от нее. Один из них - дата создания, один - идентификатор, который группирует записи по определенному идентификатору претензии, а конечным является ПК. Я хочу найти запись с максимальной датой создания в каждой группе идентификаторов требований. Я выбираю MAX (дату создания) и Идентификатор претензии (cpe.fmgcms_cpeclaimid) и группировку по идентификатору претензии. Но мне нужен PK из этих записей (cpe.fmgcms_claimid), и если я попытаюсь добавить его в предложение select, я получаю сообщение об ошибке. И я не могу добавить его в свою группу, потому что тогда он сбросит мою предполагаемую группировку. Кто-нибудь знает какие-либо обходные пути для этого? Вот пример моего кода:

Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid 
from Filteredfmgcms_claimpaymentestimate cpe
where cpe.createdon < 'reportstartdate'
group by cpe.fmgcms_cpeclaimid

Это результат, который я хотел бы получить:

Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid, cpe.fmgcms_claimid 
from Filteredfmgcms_claimpaymentestimate cpe
where cpe.createdon < 'reportstartdate'
group by cpe.fmgcms_cpeclaimid

Ответ 1

Столбцы в результирующем наборе запроса select с предложением group by должны быть:

выражение, используемое как один из критериев group by, или...
совокупная функция, или...
буквальное значение

Итак, вы не можете делать то, что хотите сделать в одном простом запросе. Первое, что нужно сделать, это четко сформулировать ваше выражение о проблеме, например:

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

Учитывая

create table dbo.some_claims_table
(
  claim_id     int      not null ,
  group_id     int      not null ,
  date_created datetime not null ,

  constraint some_table_PK primary key ( claim_id                ) ,
  constraint some_table_AK01 unique    ( group_id , claim_id     ) ,
  constraint some_Table_AK02 unique    ( group_id , date_created ) ,

)

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

select group_id ,
       date_created = max( date_created )
from dbo.claims_table
group by group_id

Это дает вам требуемые критерии выбора (1 строка на группу с двумя столбцами: group_id и дата создания воды), чтобы заполнить первую часть требования (выбор отдельной строки из каждой группы. Это должно быть виртуальная таблица в вашем последнем запросе select:

select *
from dbo.claims_table t
join ( select group_id ,
       date_created = max( date_created )
       from dbo.claims_table
       group by group_id
      ) x on x.group_id     = t.group_id
         and x.date_created = t.date_created

Если таблица не уникальна date_created внутри group_id (AK02), вы можете получить повторяющиеся строки для данной группы.

Ответ 2

Вы можете сделать это с помощью PARTITION и RANK:

select * from
(
    select MyPK, fmgcms_cpeclaimid, createdon,  
        Rank() over (Partition BY fmgcms_cpeclaimid order by createdon DESC) as Rank
    from Filteredfmgcms_claimpaymentestimate 
    where createdon < 'reportstartdate' 
) tmp
where Rank = 1

Ответ 3

Прямой ответ заключается в том, что вы не можете. Вы должны выбирать либо совокупность, либо то, что вы группируете.

Итак, вам нужен альтернативный подход.

1). Возьмите текущий запрос и присоедините его к базовым данным.

SELECT
  cpe.*
FROM
  Filteredfmgcms_claimpaymentestimate cpe
INNER JOIN
  (yourQuery) AS lookup
    ON  lookup.MaxData           = cpe.createdOn
    AND lookup.fmgcms_cpeclaimid = cpe.fmgcms_cpeclaimid

2). Используйте CTE, чтобы сделать все это за один раз...

WITH
  sequenced_data AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARITION BY fmgcms_cpeclaimid ORDER BY CreatedOn DESC) AS sequence_id
  FROM
    Filteredfmgcms_claimpaymentestimate
  WHERE
    createdon < 'reportstartdate'
)
SELECT
  *
FROM
  sequenced_data
WHERE
  sequence_id = 1

ПРИМЕЧАНИЕ. Использование ROW_NUMBER() обеспечит только одну запись за fmgcms_cpeclaimid. Даже если несколько записей связаны с одним и тем же значением createdon. Если вы можете иметь привязки и хотите, чтобы все записи имели одинаковое значение createdon, используйте RANK().

Ответ 4

Вы можете join использовать таблицу для себя, чтобы получить PK:

Select cpe1.PK, cpe2.MaxDate, cpe1.fmgcms_cpeclaimid 
from Filteredfmgcms_claimpaymentestimate cpe1
INNER JOIN
(
    select MAX(createdon) As MaxDate, fmgcms_cpeclaimid 
    from Filteredfmgcms_claimpaymentestimate
    group by fmgcms_cpeclaimid
) cpe2
    on cpe1.fmgcms_cpeclaimid = cpe2.fmgcms_cpeclaimid
    and cpe1.createdon = cpe2.MaxDate
where cpe1.createdon < 'reportstartdate'

Ответ 5

То, о чем вы спрашиваете, сэр, является ответом RedFilter. Этот ответ также помогает понять, почему группа - это как-то более простая версия или раздел: SQL Server: разница между PARTITION BY и GROUP BY поскольку он изменяет способ вычисления возвращаемого значения, и поэтому вы можете (каким-то образом) вернуть группу столбцов, не возвращаясь.