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  lt   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  lt   reportstartdate  group by cpe fmgcms cpeclaimid

User · Answer

You can join the table on itself to get the 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  lt   reportstartdate

User · Answer

What you are asking  Sir  is as the answer of RedFilter  This answer as well helps in understanding why group by is somehow a simpler version or partition over      SQL Server  Difference between PARTITION BY and GROUP BY since it changes the way the returned value is calculated and therefore you could  somehow  return columns group by can not return

User · Answer

You can use as below  Select X a  X b  Y c from                   Select X a as a  sum  b  as sum b from name table X                 group by X a X left join from name table Y on Y a   X a  Example  CREATE TABLE  products       product name VARCHAR MAX       code varchar 3       list price  numeric  8  2  NOT NULL     INSERT INTO  products VALUES   paku    ACE   2000  INSERT INTO  products VALUES   paku    ACE   2000  INSERT INTO  products VALUES   Dinding    ADE   2000  INSERT INTO  products VALUES   Kaca    AKB   2000  INSERT INTO  products VALUES   paku    ACE   2000   --SELECT   FROM  products  SELECT distinct x code  x SUM PRICE  product name FROM  SELECT code  SUM list price  as SUM PRICE From  products                 group by code x left join  products y on y code x code  DROP TABLE  products

User · Answer

The direct answer is that you can t   You must select either an aggregate or something that you are grouping by   So  you need an alternative approach   1   Take you current query and join the base data back on it  SELECT   cpe   FROM   Filteredfmgcms claimpaymentestimate cpe INNER JOIN    yourQuery  AS lookup     ON  lookup MaxData             cpe createdOn     AND lookup fmgcms cpeclaimid   cpe fmgcms cpeclaimid   2   Use a CTE to do it all in one go     WITH   sequenced data AS     SELECT            ROW NUMBER   OVER  PARITION BY fmgcms cpeclaimid ORDER BY CreatedOn DESC  AS sequence id   FROM     Filteredfmgcms claimpaymentestimate   WHERE     createdon  lt   reportstartdate    SELECT     FROM   sequenced data WHERE   sequence id   1   NOTE  Using ROW NUMBER   will ensure just one record per fmgcms cpeclaimid   Even if multiple records are tied with the exact same createdon value   If you can have ties  and want all records with the same createdon value  use RANK   instead

User · Answer

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

User · Answer

Thing I like to do is to wrap addition columns in aggregate function  like max     It works very good when you don t expect duplicate values   Select MAX cpe createdon  As MaxDate  cpe fmgcms cpeclaimid  MAX cpe fmgcms claimid  As fmgcms claimid  from Filteredfmgcms claimpaymentestimate cpe where cpe createdon  lt   reportstartdate  group by cpe fmgcms cpeclaimid

User · Answer

You can do this with PARTITION and 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  lt   reportstartdate     tmp where Rank   1

[sql] Select a Column in SQL not in Group By

Examples related to sql

Examples related to sql-server-2008

Examples related to tsql

Examples related to greatest-n-per-group