sqlserver get the first record in each group
In daily life,We often need to record some operations,Log-like operations,The last record is valid data.And maybe they belong to different aspects and functions,In database terms,Is to find a piece of data in each group.What we want to achieve is to get the first data from each group in sqlserver.
The valid data we want to get from above 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 introduces the oracle query to retrieve the first record in each group
oracle query:get the first record in each group
Group by type field,code sort, take out the first record in each group
select type, min (code) from group_info group by type;
Note:The columns after select must be included in the group by clause or included with the aggregate function.Otherwise there will be syntax errors.
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; </pre> </div> <p> The over () involved here is the analysis function of oracle </p> <p> Refer to the sql reference documentation: </p> <p> 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. </p> <p> analytic functions are the last set of operations performed in a query except for the final order by clause. all joins and all where, group by, and having clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or order by clause. </p> <p> grammar structure: </p> <p> analytic_function ([arguments]) over </p> <p> (analytic_clause) </p> <p> The analytic_clause structure includes: </p> <p> [query_partition_clause] </p> <p> [order_by_clause [windowing_clause]] </p> <p> That is:function name ([parameter]) over ([partition clause] [sort clause [sliding window clause]]) </p> <p> Here, the partition clause guided by partition by is similar to group by in the grouping function. The sort clause can be regarded as order by in the select statement. </p> <p> <strong> Get only 1 data in mysql </strong> </p> <div> <pre> select * from table limit 0, 10
limit accepts one or two numeric parameters.
The parameter must be an integer constant.
If two parameters are given,The first parameter specifies the offset of the first returned record row,
The second parameter specifies the maximum number of record rows to return.
Offset of initial record line is 0 (instead of 1)
Idea:limit is used after having
select count (1), tpc_equipment_code from tb_parts_consume group by tpc_equipment_code order by count (1) desc limit 1;
- python - you may need to restart the kernel to use updated packages error
- php - coincheck api authentication doesn't work
- php - i would like to introduce the coincheck api so that i can make payments with bitcoin on my ec site
- [php] i want to get account information using coincheck api
- the emulator process for avd pixel_2_api_29 was killed occurred when the android studio emulator was started, so i would like to
- python 3x - typeerror: 'method' object is not subscriptable
- i want to call a child component method from a parent in vuejs
- xcode - pod install [!] no `podfile 'found in the project directory
- dart - flutter: the instance member'stars' can't be accessed in an initializer error