Re: select the last record in each group

[duc minh]

Mr.Peter Brawley. Thanks for watching.
This is result of Explain Extended on my query:
'1', 'SIMPLE', 'm1', NULL, 'range', 'idx3', 'idx3', '138', NULL, '3', '100.00', 'Using index condition'
'1', 'SIMPLE', 'm2', NULL, 'index', 'idx1,idx2,idx3', 'idx2', '144', NULL, '6', '50.00', 'Using where; Using index; Using join buffer (Block Nested Loop)'.

--------------------------
Sorry for my un-clarity.
I will describe more detail about my desired result.

1. Example database:
Id Name Insert_Date Other_Columns
---------------------------------
1 A 20160101 A_data_1 -- group 1
2 A 20160202 A_data_2 -- group 1
3 A 20160401 A_data_3 -- group 1
4 B 20160111 B_data_1 -- group 2
5 B 20160121 B_data_2 -- group 2
6 C 20160301 C_data_1 -- group 3

==========================================================================================
2. My query:
SET @input_date = '20160225'; -- User can change Input_date by another value
---------------------------------
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 < @input_date
)
WHERE m2.Insert_Date IS NULL
And m1.Insert_Date < @input_date;

==========================================================================================
3. About my rule,
I have to select one record that have Insert_Date > @input_date for each group and Insert_Date of it is greatest possible in each group. (Note: select one record for each group).
I will post some example:
Example 1: SET @input_date = '20160225', then:
Id Name Insert_Date Other_Columns
---------------------------------
1 A 20160101 A_data_1 -- 20160101 < @input_date but It is not greatest possible in group 1.
2 A 20160202 A_data_2 -- It's selected because 20160202 < @input_date and Its Insert_Date is greatest possible in group 1.
3 A 20160401 A_data_3 -- It's not selected because 20160401 > @input_date
4 B 20160111 B_data_1 -- 20160111 < @input_date but It is not greatest possible in group 2.
5 B 20160121 B_data_2 -- It's selected because 20160121 < @input_date and Its Insert_Date is greatest possible in group 2.
6 C 20160301 C_data_1 -- It's not selected because 20160301 > @input_date

=>result is:
Id Name Insert_Date Other_Columns
---------------------------------
2 A 20160202 A_data_2
5 B 20160121 B_data_2

------------------------------------------------------------------------------------------
Other Example: SET @input_date = '20160125', then:
Id Name Insert_Date Other_Columns
---------------------------------
1 A 20160101 A_data_1 -- It's selected because 20160101 < @input_date and Its Insert_Date is greatest possible in group 1.
2 A 20160202 A_data_2 -- It's not selected because 20160202 > @input_date.
3 A 20160401 A_data_3 -- It's not selected because 20160401 > @input_date.
4 B 20160111 B_data_1 -- 20160111 < @input_date but It is not greatest possible in group 2.
5 B 20160121 B_data_2 -- It's selected because 20160121 < @input_date and Its Insert_Date is greatest possible in group 2.
6 C 20160301 C_data_1 -- It's not selected because 20160301 > @input_date

=>result is:
Id Name Insert_Date Other_Columns
---------------------------------
2 A 20160101 A_data_1
5 B 20160121 B_data_2

hope that you understand that through my examples

Thanks