Elohim Elohim - 16 days ago 5
MySQL Question

MySQL Select distinct column for each Relational_ID where largest timestamp below X

I am attempting to find all of the individual rows with a distinct or unique

In_Column
value for each
Item_ID
(our relational_id here) where
Item_Table
= 'X' and the
Timestamp
of the row is the highest for this distinct
In_Column
+
Item_ID
+
Item_Table
but lower than the supplied value.

sample_table

In_Column End_Value Item_Table Item_ID Timestamp
----------------------------------------------------------
Length 3 Pipe 3 2016-07-29 09:00:00
Length 2 Pipe 3 2016-07-30 09:00:00
Length 5 Pipe 4 2016-07-30 11:00:00
Kg 12 Pipe 3 2016-07-29 09:00:00
Kg 25 Steel 1 2016-07-29 09:00:00


Ideal result if supplied date was current time and Item_Table = 'Pipe'

In_Column End_Value Item_Table Item_ID Timestamp
----------------------------------------------------------
Length 2 Pipe 3 2016-07-30 09:00:00
Length 5 Pipe 4 2016-07-30 11:00:00
Kg 12 Pipe 3 2016-07-29 09:00:00


The ordering doesn't matter as I will be casting the return (which will be big, there is a lot of matching rows on the table) into an array following fetching.

Sorry for not providing an example query, I have played around with some concatenated LEFT JOINs but their execution time was understandably quite long and the result set wasn't as specific as intended.

Answer

This should work for you

SELECT 
    `t1`.*
FROM 
    `your_table` `t1`
        INNER JOIN
            (
                SELECT 
                    `Item_ID`, 
                    `In_Column`,
                    MAX(`Timestamp`) AS `latest`
                FROM 
                    `your_table`
                GROUP BY 
                    `Item_ID`, `In_Column`) `t2`
        ON 
            `t1`.`Item_ID` = `t2`.`Item_ID` AND 
            `t1`.`In_Column` = `t2`.`In_Column` AND
            `t1`.`Timestamp` = `t2`.`latest`
WHERE
    `Item_Table` = 'Pipe'

Just replace your_table with correct table name.