Select a Column in SQL not in Group By

user_1603734 0
Viewed 185027

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
greatest-n-per-group sql-server-2008 tsql sql
7 Answers

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 < 'reportstartdate'
group by cpe.fmgcms_cpeclaimid
user_82216060

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_132993360
Related