CODEWITHSUNDEEP
sqlsql-server
E-r Gabriel Doronila
E-r Gabriel Doronila

Reputation: 533

How can I get the value of non-aggregated column with an aggregated column?

I have this sample table:

ColA   ColB   ColC
CBCP    25    popx
ABC1    10    ppp1
ABC1    25    ppxx
CBCP    30    xm2x

from there I would like to get these columns

ColA   ColB   ColC
ABC1    25    ppxx
CBCP    30    xm2x

what I would want is to get the row with max ColB.. But I am getting an error when I try to include the ColC in my query:

select ColA, max(ColB), ColC
from tblCaseDev
where ColB > getdate() group by ColA

this is my error..

Msg 8120, Level 16, State 1, Line 1
Column 'tblCaseDev.ColC' is invalid in the
select list because it is not contained in either
an aggregate function or the GROUP BY clause.

hope someone could help me.. thanks in advance..

Upvotes: 1

Views: 99

Answers (4)

Juan Filipe
Juan Filipe

Reputation: 403

SELECT
CaseNo,Date,Remarks,

(SELECT max(cast(Date as datetime)) FROM tblCaseDev subc WHERE subc.CaseNo=c.CaseNo Group by c.CaseNo) AS MaxEntryDate

FROM tblCaseDev c 

order by CaseNo

Upvotes: 2

Khan
Khan

Reputation: 18162

You can create an aggregate query first, then join the original table to the aggregate query.

Example:

SELECT
    A.CaseNo,
    A.Date,
    B.Remarks
FROM (
    SELECT
        CaseNo,
        MAX(Date)
    FROM tblCaseDev 
    WHERE Date > GetDate()
    GROUP BY CaseNo
) A 
JOIN tblCaseDev B
    ON A.CaseNo = B.CaseNo
    AND A.Date = B.Date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270361

You want to use the row_number() window function:

select CaseNo, "Date", Remarks
from (select t.*, row_number() over (partition by caseno order by date desc) as seqnum
      from tblCaseDev t
      where date > getdate()
     ) t
where seqnum = 1;

EDIT:

You can do this the old fashioned way if you don't have row_number():

select t.*
from tblCaseDev t join
     (select caseno, max(date) as maxdate
      from tblCaseDev 
      group by caseno
     ) tsum
     on t.caseno = tsum.caseno and t.date = tsum.maxdate

Upvotes: 1

John Woo
John Woo

Reputation: 263803

You can use ROW_NUMBER() which generates sequential number for every group ColA and ordered by ColB in descending.

SELECT     ColA, ColB, ColC
FROM 
          (
               SELECT    ColA, ColB, ColC,
                         ROW_NUMBER() OVER(PARTITION BY ColA 
                                           ORDER BY ColB DESC) rn
               FROM tablename
          ) x
WHERE     rn = 1

If you want to get multiple records which ties up in ColB, you may want to change it to DENSE_RANK().

Upvotes: 0

Related Questions