Search code examples
group-bymaxdb

Select columns by min value - group by

When I read this post: SQL: Group by minimum value in one field while selecting distinct rows

the following solution did not make sense to me and I wonder how it can possibly work:

SELECT id, min(record_date), other_cols 
FROM mytable
GROUP BY id

This does NOT work on my database, and it somehow seems illogical to me (although it does work in the SQL fiddle example). My DBMS complains that

Column must be group column: other_cols

I am using MaxDB - is this a DBMS specific issue?

Solution

  • It works in some RDBMSs like MySQL. MySQL docs says:

    MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group.

    But in case of SQL Server, you need to include all fields in the GROUP BY clause. Otherwise, it will report an error like:

    Column 'ColName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    See MSDN.