MySQL Forums
Forum List  »  Newbie

Re: select the last record in each group
Posted by: duc minh
Date: March 25, 2016 09:34PM

Dear Mr.Peter Brawley.
First, let me update my result that I want.
Example:
Id Name Insert_Date Other_Columns
-------------------------------------
1 A 20160101 A_data_1
2 A 20160202 A_data_2
3 A 20160401 A_data_3
4 B 20160111 B_data_1
5 B 20160121 B_data_2
6 C 20160301 C_data_1

If user input 20160225, then result is:
Name Insert_Date Other_Columns
---------------------------------
A 20160201 A_data_2
B 20160121 B_data_2

(Note: Record with name = "C" is not selected because 20160301 > 20160225))

Here is my query:
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.Insert_Date < m2.Insert_Date
AND m2.Insert_Date < '20160325'
)
WHERE m2.Insert_Date IS NULL
And m1.Insert_Date < '20160325';

That query return right result that i want, However It's very slowly in the case there is not any record fit with input date.

And here is my create table query:
CREATE TABLE `messages` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(1) DEFAULT NULL,
`Insert_Date` varchar(45) DEFAULT NULL,
`Other_Columns` varchar(45) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `idx1` (`Name`) USING BTREE,
KEY `idx2` (`Name`,`Insert_Date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
SELECT m1.*
FROM messages m1 LEFT JOIN messages m2
ON (m1.name = m2.name AND m1.Insert_Date < m2.Insert_Date
AND m2.Insert_Date < '20160225'
)
WHERE m2.Insert_Date IS NULL
And m1.Insert_Date < '20160225';

Thanks

Options: ReplyQuote


Subject
Written By
Posted
Re: select the last record in each group
March 25, 2016 09:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.