Group By include non-aggregate field

Question

I have a table with duplicate values in field2 and field1 has values I want to preserve in a query.

field1  field2
 Bob       2
 Bob       2
 Bob       3
 Bob       3

This query uses a group by clause:

select field2
rom table
group by field2

As we all know I cannot include field1 in the select. I need to include field1 in my output. Not sure how to accomplish this.

Answer 1

What do you want to preserve from field1? The MIN value?

  select field2, min(field1) field1
    from table
group by field2

Perhaps the MAX value?

  select field2, max(field1) field1, count(field2) total
    from table
group by field2

You have to put an aggregate against it, otherwise it won't make sense. Consider this data:

field1   field2
Bob      2
Jim      2
Tim      2

Okay, so you want field1 with field2. You group by field2 to give you a single row with field2 in the output. You cannot have all 3 of (Bob,Jim,Tim) in the row, and that's the reason why you must put an aggregate against it to logically choose just one value (or an aggregate of several, such as an average of a numeric column).