Maximum records for SQL grouping

To obtain the first record after grouping by group, we need to combine order by. Originally, we used group by to retrieve all groups, then, we can use order by to sort the data in the group by desc and retrieve the first shard.

First, let's look at the group by statement usage.

Group by statement
The group by statement is used in combination with the aggregate function to GROUP result sets based on one or more columns.

Click SQL GROUP BY syntax to view the source file

The Code is as follows:	Copy code
SELECT column_name, aggregate_function (column_name) FROM table_name WHERE column_name operator value Group by column_name

Use the same number of SQL Aggregate functions together with SQL statements to provide certain database tutorial tables for grouping (the result dataset method ).

Instance

Test SQL

 

The Code is as follows:

Copy code

Create table if not exists 'test '( 'Id' int (10) unsigned not null auto_increment, 'Install' int (10) unsigned not null, 'Day' int (10) unsigned not null, 'Aid 'int (10) unsigned not null, Primary key ('id ') ) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 12; Insert into 'test' ('id', 'install', 'day', 'aid ') VALUES (1, 'www. bKjia. c0m', 20120308, 1 ), (2, 2321,201 20309, 2 ), (3, 1236,201, 3 ), (5, 'www .hzhuti.com ', 20120309, 1 ), (6, 2312,201 20310, 1 ), (7, 1432,201 20311, 1 ), (8, 2421,201 20308, 2 ), (9, 4245,201, 2 ), (10, 'www. bKjia. c0m', 20120310, 2 ), (11,412,201 20308, 3 );

Implement SQL statements

The Code is as follows:	Copy code
Select a. * FROM test, (SELECT aid, MAX (day) max_day FROM test group by aid) B Where a. aid = B. aid and a. day = B. max_day Order by a. install DESC

In this way, we only need to take the first part of the desc sorting of the set.

Let's look at another mssql server instance.

 

For example, table1 (a, B, c, d)

The Code is as follows:	Copy code
A B c d 1 0 c1 d1 1 1 c2 d2 1 3 c3 d3 4 0 c4 d4 5 1 c5 d5 5 2 c6 d6 6 1 c7 d7 6 4 c8 d8

What I want to get is a group by a. The record with the maximum value of B in each group is

The Code is as follows:	Copy code
A B c d 1 3 c3 d3 4 0 c4 d4 5 2 c6 d6 6 4 c8 d8

Method 1

The Code is as follows:	Copy code
Select * from table1 where B in (select max (B) from table1 group by );

Method 2

The Code is as follows:	Copy code
Select * from table1 a where B = (select max (B) from table1 where a = a.)

Now let's talk about this much and hope it will be useful to everyone.