Mysql, consult data grouping after the first question-CodePudding

Existing table data as follows, need according to the column1, column2 grouping, and then take column4 the latest one, there column4 latest the same time, this kind of circumstance how to pick up one of them? Thank you,

Column1, column2, column3 column4
1,2,3,2019-06-01:00:00:00
1,2,4,2019-06-01:00:00:00
5,6,7,2019-06-01:00:03:00
8,9,10,2019-06-01:01:00:00
8,9,11,2019-06-01:01:00:00

CodePudding user response:

The SELECT column1, column2, Max (column4)
The from table_column
Group by column1, column2

CodePudding user response:

First group, the primary key sort, take the first
Select fields from table_column group by column1, column2 order by primary key desc limit 1;

CodePudding user response:

refer to the second floor oneouto response:

group first, pour in the primary key sorting, take the first
Select fields from table_column group by column1, column2 order by primary key desc limit 1;

You this is not the right result, I want to get the result is as follows:
Column1, column2, column3 column4
1,2,3,2019-06-01 00:00:00
5,6,7,2019-06-01 00:03:00
8,9,10,2019-06-01 01:00:00

the list is not the primary key, want to column1, give priority to key column2, grouping column4 time after the latest one, column4 latest time there may be N have the same article, can randomly pick up one of the it doesn't matter, is to complete the whole data,

CodePudding user response:

reference 1st floor ZJHZ_ leaf response:

SELECT column1, column2, Max (column4)
The from table_column
Group by column1, column2

You taken out of the results is not complete, article if each of the data is 100, I want to give priority to key 2 two columns, grouping after time a new article, the latest time there may be N have the same article, can randomly pick up one of the it doesn't matter, is to complete the whole data,
Example to achieve results as follows,
Column1, column2, column3 column4
1,2,3,2019-06-01 00:00:00
5,6,7,2019-06-01 00:03:00
8,9,10,2019-06-01 01:00:00

CodePudding user response:

The SELECT column1, column2, Max (column3), Max (column4)
The from table_column
Group by column1, column2

CodePudding user response:

reference 5 floor ZJHZ_ leaf reply:

SELECT column1, column2, Max (column3), Max (column4)
The from table_column
Group by column1, column2

I this is just an example, the actual may have 100 column data, and then just want to take a column after the latest time a complete data,

CodePudding user response:

reference bin_fei reply: 3/f

Quote: refer to the second floor oneouto response:

First group, the primary key sort, take the first
Select fields from table_column group by column1, column2 order by primary key desc limit 1;

You this is not the right result, I want to get the result is as follows:
Column1, column2, column3 column4
1,2,3,2019-06-01 00:00:00
5,6,7,2019-06-01 00:03:00
8,9,10,2019-06-01 01:00:00
the list is not the primary key, want to column1, give priority to key column2, grouping column4 time after the latest one, column4 latest time there may be N have the same article, can randomly pick up one of the it doesn't matter, is to complete the whole data,

The select field, unix_timestamp (column4) as colu from table_column group by column1, column2 order by colu desc limit 1;
Unix_timestamp timestamp

CodePudding user response:

Select * from (
Select a. *,
If (@ column1=column1 and @ column2=column2, @ rownum:=@ rownum + 1, @ rownum:=1) AS an Rn,
@ column1:=column1 ncolumn1,
@ column2:=column2 ncolumn2
The from table_column a, (select @ rownum:=0, @ column1:=null, @ column2:=null) b
C)
Where an rn=1

CodePudding user response:

Should be need to take the first packet ordering
Select a t1. * from
(SELECT column1, column2, Max (column3) column3, Max (column4) column4 from table_column group by column1, column2) t1
Where 1 & gt; (select count (*) from (select column1, column2, Max (column3) column3, Max (column4) column4 from table_column group by column1, column2) t2 where t1. Column1=t2. Column1, t1, column2=t2. Column2 and t1. Column4 & lt; T2. Column4)

CodePudding user response:

refer to 7th floor oneouto response:

Quote: reference bin_fei reply: 3/f

Quote: refer to the second floor oneouto response:

First group, the primary key sort, take the first
Select fields from table_column group by column1, column2 order by primary key desc limit 1;

You this is not the right result, I want to get the result is as follows:
Column1, column2, column3 column4
1,2,3,2019-06-01 00:00:00
5,6,7,2019-06-01 00:03:00
8,9,10,2019-06-01 01:00:00
the list is not the primary key, want to column1, give priority to key column2, grouping column4 time after the latest one, column4 latest time there may be N have the same article, can randomly pick up one of the it doesn't matter, is to complete the whole data,

The select field, unix_timestamp (column4) as colu from table_column group by column1, column2 order by colu desc limit 1;
Unix_timestamp timestamp

Limit 1 is finally on the full results, so only to get a data, it is not right, and your own experiment, I want to take is the latest in each group after group of a, not all of them to get a result,

CodePudding user response:

references to the tenth floor bin_fei response:

Quote: refer to 7th floor oneouto response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull