Select a Column in SQL not in Group By

Question

I have been trying to find some info on how to select a non-aggregate column that is not contained in the Group By statement in SQL, but nothing I've found so far seems to answer my question. I have a table with three columns that I want from it. One is a create date, one is a ID that groups the records by a particular Claim ID, and the final is the PK. I want to find the record that has the max creation date in each group of claim IDs. I am selecting the MAX(creation date), and Claim ID (cpe.fmgcms_cpeclaimid), and grouping by the Claim ID. But I need the PK from these records (cpe.fmgcms_claimid), and if I try to add it to my select clause, I get an error. And I can't add it to my group by clause because then it will throw off my intended grouping. Does anyone know any workarounds for this? Here is a sample of my code:

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

This is the result I'd like to get:

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

Answer 1

The columns in the result set of a select query with group by clause must be:

• an expression used as one of the group by criteria , or ...
• an aggregate function , or ...
• a literal value

So, you can't do what you want to do in a single, simple query. The first thing to do is state your problem statement in a clear way, something like:

I want to find the individual claim row bearing the most recent creation date within each group in my claims table

Given

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 ) ,

)

The first thing to do is identify the most recent creation date for each group:

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

That gives you the selection criteria you need (1 row per group, with 2 columns: group_id and the highwater created date) to fullfill the 1st part of the requirement (selecting the individual row from each group. That needs to be a virtual table in your final select query:

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

If the table is not unique by date_created within group_id (AK02), you you can get duplicate rows for a given group.