SQL gets the first record of the method (SQL Server, Oracle, MySQL database) _mssql

SQL Server gets the first record in each group

In daily life, we often need to record some operations, similar to the operation of the log, the last record is valid data, and maybe they belong to different aspects, functions below, from the terminology of the database, is to find a piece of data in each group. Here's what we're going to do is implement the first piece of data from each group in SQL Server.

Example

The valid data we want to obtain from the above are:

The corresponding SQL statement looks like this:

SELECT * FROM t1 t where id = (select top 1 ID from t1 where grp = t.grp ORDER BY createtime Desc)

Let's introduce the Oracle query to remove the first record from each group

Oracle query: Remove the first record from each group

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
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.
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.

Syntax structure:

Analytic_function ([arguments]) 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.

Only 1 data is available in MySQL

SELECT * FROM table LIMIT 0, 10

LIMIT accepts one or two numeric parameters.

parameter must be an integer constant.

If you give two arguments, the first parameter specifies the offset of the first row that returns the record.

The second parameter specifies the maximum number of rows to be returned.

The offset of the initial record line is 0 (instead of 1)

Idea: Limit used after having

Your own example:

Select COUNT (1), Tpc_equipment_code from Tb_parts_consume GROUP by Tpc_equipment_code Order by count (1) DESC LIMIT 1;