GROUP BY only primary key, but select other values GROUP BY only primary key, but select other values sql sql

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: here:

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