MySQL Sorted first in group

– The structure of the table ' test '
–

CREATE TABLE IF not EXISTS ' test ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (+) is not NULL,
' Phone ' int (one) is not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8;

–
– Export the data in the table ' test '
–

INSERT into ' test ' (' id ', ' name ', ' phone ') VALUES
(1, ' a ', 1234),
(2, ' a ', 3333),
(3, ' B ', 555),
(4, ' B ', 6773),
(5, ' a ', 743),
(6, ' C ', 95434);

Check it out,
SELECT * from ' test ' GROUP by name
Get
ID Name Phone
1 a 1234
3 B 555
6 C 95434

But what if we want the name with the largest ID?
SELECT Max (ID), Id,name,phone from test group by name
Get
Max (ID) ID name phone
5 1 A 1234
4 3 B 555
6 6 C 95434
As you can see, although the maximum ID for each name is obtained, the other data remains the first row of each name
Using subqueries
SELECT * FROM (SELECT * from test ORDER BY id DESC) T-GROUP by name
Get
ID Name Phone
5 a 743
4 B 6773
6 C 95434
This is what we want to do, but this practice is equivalent to copying the entire table once and estimating the efficiency is low in the case of very many rows.
Well, with this subquery,
SELECT * FROM Test T where ID in (SELECT MAX (ID) from test group by name)
Get
ID Name Phone
4 B 6773
5 a 743
6 C 95434
However, this seed query may be slower because of the use of in, and in a large number of cases. Not sure, no time for testing.
In addition, there is a way, but the principle I am also a little confused, just to see someone online,
SELECT * FROM Test t INNER join (SELECT * FROM test ORDER by id desc) T2 on t.id=t2.id GROUP by T.name
Get
ID name phone ID name phone
5 a 743 5 a 743
4 B 6773 4 B 6773
6 C 95434 6 C 95434
In order to improve efficiency, think of the view, first by the ID desc row view, then group by name, is not the equivalent of a subquery?
CREATE VIEW ' Testv ' as select ' Test ', ' ID ' as ' id ', ' test '. ' Name ' as ' name ', ' Test '. ' Phone ' as ' phone ' from ' Test ' order by ' Test '. ' ID ' desc;
View set up, then query
SELECT * from ' Testv ' GROUP by name
It turned out to be
ID Name Phone
1 a 1234
3 B 555
6 C 95434
And in the original table with
SELECT * from ' test ' GROUP by name
. It seems that the view and the real table are different after all.

Add:
Surf the internet again, originally the second seed Query method online is not like that, although I wrote in this example also succeeded, but, perhaps the other table will be wrong? There is no time to test. Online method is
SELECT * FROM Test t inner join (SELECT MAX (ID) as Id,name from test group by name) T2 on T.id=t2.id and T.name=t2.name
Get
ID Name Phone ID name
5 a 743 5 a
4 B 6773 4 b
6 C 95434 6 C
In addition, replacing the test table in the above query methods with TESTV can get the correct results, even though the order by is a bit different.
The above sort is for only one field, and two or more fields can also be used similar to
SELECT * FROM (SELECT * from test ORDER BY id DESC) T-GROUP by name
In this way, multiple fields can be used in a subquery to order by
Delete the original test below and rebuild
–
– The structure of the table ' test '
–

CREATE TABLE IF not EXISTS ' test ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (+) is not NULL,
' Month ' int (one) is not NULL,
' Serial ' int (one) is not NULL,
' Other ' varchar () is not NULL,
PRIMARY KEY (' id ')
) Engine=myisam DEFAULT Charset=utf8;

–
– Export the data in the table ' test '
–

INSERT into ' test ' (' id ', ' name ', ' Month ', ' serial ', ' other ') VALUES
(1, ' A ', 200807, 2, ' aaa1′),
(2, ' a ', 200805, 2, ' aaa2′),
(3, ' B ', 200805, 3, ' bbb3′),
(4, ' B ', 200805, 4, ' bbb4′),
(5, ' a ', 200805, 1, ' aaa5′),
(6, ' C ', 200807, 5, ' ccc6′),
(7, ' B ', 200807, 8, ' bbb7′),
(8, ' C ', 200807, 3, ' ccc8′),
(9, ' a ', 200805, 6, ' aaa9′);

Inquire
SELECT * FROM [SELECT * FROM Test order BY month Desc,serial Desc] t GROUP by name
Get
ID Name Month serial other
1 A 200807 2 AAA1
7 B 200807 8 Bbb7
6 C 200807 5 CCC6
Change the Sort method
SELECT * FROM [SELECT * FROM Test order BY month Asc,serial Desc] t GROUP by name
Get
ID Name Month serial other
9 A 200805 6 aaa9
4 B 200805 4 Bbb4
6 C 200807 5 CCC6
We showed the results as we asked.

This entry was posted in excellence article category, posted in MySQL, SQL tab. Add a pinned link to your Favorites folder.

SELECT * FROM (SELECT * to households ORDER BY id Desc) H GROUP by e_id
SELECT * FROM households t where id = (select max (id) from households where e_id = t.e_id)
SELECT * from TB t where is exists (select 1 from TB where site = t. Site and time > t. Time)

MySQL Sorted first in group