SQL & PL/SQL

Read the documentation on the "Group_By" clause --> https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2182483

group_by_clause
Specify the GROUP BY clause if you want the database to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group. If this clause contains CUBE or ROLLUP extensions, then the database produces superaggregate groupings in addition to the regular groupings.
Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list.
The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.
If the NLS_SORT parameter has a setting other than BINARY and the NLS_COMP parameter is set to LINGUISTIC, then expression values are compared linguistically according to the linguistic definition specified in NLS_SORT to decide if they are equal and therefore belong to the same group. When character values are compared linguistically for GROUPBY, they are first transformed to collation keys and then compared like RAW values. The collation keys are the same values that are returned by the function NLSSORT and are subject to the same restrictions that are described in "NLSSORT". As a result of these restrictions, two values may compare as linguistically equal and be grouped together if they do not differ in the prefix that was used to produce the collation key, even if they differ in the rest of the value.

Hi,

As you discovered (and as Carlos said) you can use GROUP BY without an aggregate function.

However, if you're ever tempted to do that, stop and think if it's the best way.  In you first example, SELECT DISTINCT might be clearer.

As for the second example (why you get the error) here's a rule, the ABC's of GROUP BY:

When you use a GROUP BY clause and/or an aggregate function, then everything in the SELECT clause must be

(A) an Aggregate function,

(B) one of the "group By" expressions,

(C) a Constant, or

(D) something that Depends entirely on the above.

You can use all the selected columns in the group by columns

Then you will find the distinct columns. It will be same if you will try use distinct.

As

select job_id, max_salary from jobs group by job_id,max_salary

Will be same as

select distinct job_id, max_salary from jobs

Thank you, this answers my question

Solves my question, thank you