Home>

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.

example

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

method one:

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.

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;
</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

Own example:

select count (1), tpc_equipment_code from tb_parts_consume group by tpc_equipment_code order by count (1) desc limit 1;
  • Previous JavaWeb Builds Online Book Store Graduation Design
  • Next Java implements the method of sorting objects in List