SQL method for obtaining the first record (sqlserver, oracle, mysql database), sqlservermysql

SQL method for obtaining the first record (sqlserver, oracle, mysql database), sqlservermysql

Sqlserver get the first record in each group

In daily life, we often need to record some operations, similar to log operations. The final records are valid data, and they may belong to different aspects and functions, in terms of databases, it is to find a piece of data in each group. The following is how to extract the first data from each group in sqlserver.

Example

The valid data we need to obtain is:

The corresponding SQL statement is as follows:

select * from t1 t where id = (select top 1 id from t1 where grp = t.grp order by createtime desc )

The following describes how to retrieve the first record in each group through oracle query.

Oracle query: retrieve the first record in each group

Group by type field, sort by code, and retrieve the first record in each group

Method 1:

select type,min(code) from group_info group by type;

Note: The columns after the select statement must be in the group by clause or be included by an aggregate function. Otherwise, a syntax error occurs.

Method 2:

SELECT * FROM(SELECT z.type , z.code ,ROW_NUMBER()OVER(PARTITION BY z.type ORDER BY z.code) AS code_idFROM group_info z)WHERE code_id =1;

The over () mentioned here is an oracle analysis function.

Refer to the SQL reference document:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.
Analytic functions are the last set of operations saved med in a query failed t for the final order by clause. all joins and all WHERE, group by, and HAVING clses 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 includes:

[Query_partition_clause]
[Order_by_clause [windowing_clause]

That is, function name ([parameter]) over ([partition clause] [sort clause [Sliding Window clause])

Here, the PARTITION clause guided by partition by is similar to the group by clause in the clustering function. The sort clause can be considered as the order BY clause in the select statement.

Mysql obtains only one data entry.

SELECT * FROM Table LIMIT 0, 10

LIMIT accepts one or two numeric parameters.

The parameter must be an integer constant.

If two parameters are specified, the first parameter specifies the offset of the first returned record row,

The second parameter specifies the maximum number of returned record rows.

The offset of the initial record row is 0 rather than 1)

Idea: limit is used after having

Example:

select count(1),tpc_equipment_code from tb_parts_consume GROUP BY tpc_equipment_code ORDER BY count(1) DESC LIMIT 1;