GROUP BY only primary key, but select other values

In more recent versions of MySql you might have sql_mode=only_full_group_by enabled which doesn't allow to select non-aggregated columns when using group by i.e. it forces you to use a function like max() or avg() or group_concat(), sometimes you just want any value.

This flag is enabled by default in MySql 5.7.

The function any_value() is available when that flag is enabled.

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

select t.index, any_value(t.insert_date)from my_table tgroup by t.index;

More information here:https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_byand here:https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

In Postgres (not in MySQL, though), you could use DISTINCT ON to pick a single, consistent row per value (or group of values) without aggregating them:

SELECT DISTINCT ON (n.node_id)       *                 -- select any or all columns of all joined tablesFROM   {a couple of joined tables}JOIN   nodes n USING (node_id)

That gives you a single, arbitrary row for each node_id. to pick a specific row, add:

ORDER  BY n.node_id, ... -- what to sort first?

.. add more ORDER BY items to pick a specific row. Details:
Select first row in each GROUP BY group?

You could try converting the other columns into aggregates:

SELECT myPrimaryKey, MAX(otherThing)FROM myTableGROUP BY myPrimaryKey