Oracle query: Group query, remove the first record in each group __oracle

Group By Type field, sort by code, and remove the first record from each group

Method One:

Select Type,min (code) from Group_info
Group By type;

Note: The columns following the select are included in the GROUP BY clause, or with aggregate functions, otherwise there will be a syntax error.

Method Two:

SELECT * FROM (
SELECT Z.type, Z.code, Row_number ()
Over (PARTITION by Z.type Order by Z.code) as code_id
From Group_info Z
)
WHERE code_id = 1;

Here the Over () is the Oracle analysis function

Refer to SQL Reference documentation:

Analytic functions compute a aggregate value based on a group of rows. They differ from aggregate functions in, They return multiple rows for each group.

Analyzing an aggregate function evaluates a value based on a set of rows. They are different from aggregate functions that return multiple rows per group.

Analytic functions are the last set of operations performed into a query except for the final ORDER BY clause. All joins and a WHERE, GROUP by, and has clauses are completed before the analytic functions. are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Executes a query in the last set of operations of the parse function, except for the last ORDER BY clause. All connections and places, GROUP by and previous terms complete the analysis processing function. Therefore, the parse function can only appear in the select list or the ORDER BY clause.

Syntax structure:

 over
 (Analytic_clause)

The Analytic_clause structure consists of:

[Query_partition_clause]
[Order_by_clause [Windowing_clause]]

That is: function name ([parameter]) over ([partition clause] [SORT clause [slide window clause]])

Here partition by-boot partition clauses are similar to group by in a clustered function, and the sort clause can be viewed as an order by in a SELECT statement.