Select a Column in SQL not in Group By

by

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.

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. to select all the employees who have joined most recently under king
  3. Get top 1 row of each group
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Unpivot with column name
  6. How to generate a range of numbers between two numbers?
  7. SET versus SELECT when assigning variables?
  8. Multiple INSERT statements vs. single INSERT with multiple VALUES
  9. Convert varchar into datetime in SQL Server
  10. Generate Dates between date ranges
  11. How to print VARCHAR(MAX) using Print Statement?
  12. How to update Identity Column in SQL Server?
  13. Common Table Expression, why semicolon?
  14. T-SQL split string based on delimiter
  15. Concatenate values based on ID
  16. Splitting delimited values in a SQL column into multiple rows
  17. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  18. How does this CASE expression reach the ELSE clause?
  19. TransactSQL to run another TransactSQL script
  20. Store multiple bit values in a single table column
  21. Stored procedure that exports data into csv files only exports to one file
  22. How can I group time by hour or by 10 minutes?
  23. SQL Server equivalent to MySQL enum data type?
  24. Grant SELECT permission on a view, but not on underlying objects
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. Repeat Rows N Times According to Column Value
  27. SQL take just the numeric values from a varchar
  28. COALESCE Function in TSQL
  29. Execute sp_executeSql for select…into #table but Can’t Select out Temp Table Data
  30. Select rows where column value has changed

Leave a Comment