CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
python_enthusiast
python_enthusiast

Reputation: 346

Group by in query taking last item in text column while aggregating int columns

Can someone please help me with aggregating the int columns and taking the col4 text value corresponding to the latest col2 date column.

Lets say we have the following table 'Table1':

col1 col2 col3 col4
Name 2021-01-01 50 First_Appearance
Name 2021-01-02 60 Second_Appearance
Name 2021-01-04 40 Third_Appearance
Place 2021-01-01 50 First_Appearance
Place 2021-01-03 60 Second_Appearance
Place 2021-01-05 40 Third_Appearance
Place 2021-01-04 40 Fourth_Appearance 
select name, max(col2), max(col3), max(col4)
from Table1
group by col1;

Above query will give me this:

col1 col2 col3 col4
Name 2021-01-01 150 First_Appearance
Place 2021-01-05 190 First_Appearance

Can anyone help me with the query to get to the following table instead:

col1 col2 col3 col4
Name 2021-01-01 150 Third_Appearance
Place 2021-01-05 190 Fourth_Appearance

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

One method is conditional aggregation using row_number():

select col1, max(col2), sum(col3),
       max(case when seqnum = 1 then col4 end) as col4
from (select t.*,
             row_number() over (partition by col1 order by col2 desc) as seqnum
      from t
     ) t
group by col1;

Upvotes: 2

Related Questions