Is it possible to select a column without aggregating it?

Question

I'm looking for a way to display a column in my table without aggregating it (column store)

SELECT id, name, max(cost), store 
FROM test
group by 1,2,4

But in the table im using there are some row that shares the same id,name,license with different machine, and Ideally I'd like to get in return only a single row, with max(cost), if they share the same first 2 columns and only display the last column - without using it to group by.

actual outcome:

id	name	max(cost)	store
1	Joe	30	store1
1	Joe	50	store2

but my desired result will be:

id	name	max(cost)	store
1	Joe	50	store2

Can it even be done?

Any reason you do not want to use aggregation ? – vivek Aug 25 '21 at 11:18 — vivek, Aug 25 '21 at 11:18

score 0 · Answer 1 · answered Aug 25 '21 at 11:12

You seem to want the row with the maximum cost. A typical method uses row_number();

select t.*
from (select t.*,
             row_number() over (partition by id, name order by cost desc) as seqnum
      from test t
     ) t
where seqnum = 1;

No aggregation is needed.

You can also use a correlated subquery:

select t.*
from test t
where t.cost = (select max(t2.cost)
                from test t2
                where t2.id = t.id
               );

Is it possible to select a column without aggregating it?

1 Answers1