GROUP BY and ORDER

Question

I have a very simple Maria DB for home automation purpose. This is the essential extract of my ER:

TelldusActionsPerformed is a log table. It is used for keeping track of very different actions (like a temperature measurements, a door closing, a power switch turned etc) performed at different times.

I use the following query to fetch interesting data:

SELECT
    TelldusActionsPerformed.PerformedTime AS TelldusActionsPerformed_PerformedTime,
    TelldusUnits.Name AS TelldusUnits_Name,
    TelldusActionValues.ActionValue AS TelldusActionValues_ActionValue,
    TelldusActionValueTypes.Name AS TelldusActionValueTypes_Name
FROM
    TelldusActions
    INNER JOIN TelldusUnits ON TelldusActions.FK_TelldusUnit_Id = TelldusUnits.Id
    INNER JOIN TelldusActionTypes ON TelldusActions.FK_TelldusActionType_Id = TelldusActionTypes.Id
    INNER JOIN TelldusActionValues ON TelldusActions.FK_TelldusActionValue_Id = TelldusActionValues.Id
    INNER JOIN TelldusActionValueTypes ON TelldusActionValues.FK_TelldusActionValueType_Id = TelldusActionValueTypes.Id 
    INNER JOIN TelldusActionsPerformed ON TelldusActionsPerformed.FK_TelldusAction_Id = TelldusActions.Id
    WHERE TelldusUnits.Name IN ("Grovkök golvtermostat", "Huvudtermostat", "Uterum golvtermostat")
AND TelldusActionValueTypes.Name IN ("watt","temp")
ORDER BY TelldusActionsPerformed.PerformedTime DESC

This gives me a result set which looks like this

I would like to refine my query to only return the absolutely latest (time) unique combinations for the TelldusUnits.Name + TelldusActionValueTypes.Name fields:

I believe that I need to GROUP BY TelldusUnits.Name, TelldusActionValueTypes.Name to get the unique combinations? Is it possible to limit my search result to the latest unique combinations for the TelldusUnits.Name + TelldusActionValueTypes.Name fields with SQL only? How?

Edit 1:

If I run mr Murillo's script right out of the box (I only added FROM_UNIXTIME(), forgot it in my original question):

SELECT
    FROM_UNIXTIME(MAX(TelldusActionsPerformed.PerformedTime)) AS TelldusActionsPerformed_PerformedTime,
    TelldusUnits.Name AS TelldusUnits_Name,
    TelldusActionValues.ActionValue AS TelldusActionValues_ActionValue,
    TelldusActionValueTypes.Name AS TelldusActionValueTypes_Name
FROM
    TelldusActions
    INNER JOIN TelldusUnits ON TelldusActions.FK_TelldusUnit_Id = TelldusUnits.Id
    INNER JOIN TelldusActionTypes ON TelldusActions.FK_TelldusActionType_Id = TelldusActionTypes.Id
    INNER JOIN TelldusActionValues ON TelldusActions.FK_TelldusActionValue_Id = TelldusActionValues.Id
    INNER JOIN TelldusActionValueTypes ON TelldusActionValues.FK_TelldusActionValueType_Id = TelldusActionValueTypes.Id 
    INNER JOIN TelldusActionsPerformed ON TelldusActionsPerformed.FK_TelldusAction_Id = TelldusActions.Id
    WHERE TelldusUnits.Name IN ("Grovkök golvtermostat", "Huvudtermostat", "Uterum golvtermostat")
AND TelldusActionValueTypes.Name IN ("watt","temp")
GROUP BY TelldusUnits.Name, TelldusActionValues.ActionValue, TelldusActionValueTypes.Name
ORDER BY TelldusActionsPerformed.PerformedTime DESC

I get the following result set:

The ORDER BY TelldusActionsPerformed.PerformedTime DESC confuses me. The table does not seem to be ordered descendant according to PerformedTime? (PerformedTime, UNIX-timestamp is of datatype int(11) )

Edit 2:

The next update regarding to mr Murillo's commnt gave me a better sort:

SELECT
    FROM_UNIXTIME(MAX(TelldusActionsPerformed.PerformedTime)) AS TelldusActionsPerformed_PerformedTime,
    TelldusUnits.Name AS TelldusUnits_Name,
    TelldusActionValues.ActionValue AS TelldusActionValues_ActionValue,
    TelldusActionValueTypes.Name AS TelldusActionValueTypes_Name

FROM
    TelldusActions
    INNER JOIN TelldusUnits ON TelldusActions.FK_TelldusUnit_Id = TelldusUnits.Id
    INNER JOIN TelldusActionTypes ON TelldusActions.FK_TelldusActionType_Id = TelldusActionTypes.Id
    INNER JOIN TelldusActionValues ON TelldusActions.FK_TelldusActionValue_Id = TelldusActionValues.Id
    INNER JOIN TelldusActionValueTypes ON TelldusActionValues.FK_TelldusActionValueType_Id = TelldusActionValueTypes.Id
    INNER JOIN TelldusActionsPerformed ON TelldusActionsPerformed.FK_TelldusAction_Id = TelldusActions.Id
WHERE TelldusUnits.Name IN ("Grovkök golvtermostat", "Huvudtermostat", "Uterum golvtermostat")
AND TelldusActionValueTypes.Name IN ("watt","temp")
GROUP BY TelldusUnits.Name, TelldusActionValues.ActionValue, TelldusActionValueTypes.Name
ORDER BY MAX(TelldusActionsPerformed.PerformedTime) DESC

There still is a detail to take care of (according to my original question: I only wanted the query to return the absolutely latest (time) unique combinations for the TelldusUnits.Name + TelldusActionValueTypes.Name fields.

Answer 1

The working answer

I was really helped by Murillos answer even though it did not completely answered my question (I still consider it is a good answer). This is the query I finally used and (hopefully) answers my original question:

SELECT 
    OrderedResultSet.TelldusActionsPerformed_PerformedTime,
    OrderedResultSet.TelldusUnits_Name,
    OrderedResultSet.TelldusActionValues_ActionValue,
    OrderedResultSet.TelldusActionValueTypes_Name

FROM (
    SELECT
        FROM_UNIXTIME(MAX(TelldusActionsPerformed.PerformedTime)) AS TelldusActionsPerformed_PerformedTime,
        TelldusUnits.Name AS TelldusUnits_Name,
        TelldusActionValues.ActionValue AS TelldusActionValues_ActionValue,
        TelldusActionValueTypes.Name AS TelldusActionValueTypes_Name

    FROM
        TelldusActions
        INNER JOIN TelldusUnits ON TelldusActions.FK_TelldusUnit_Id = TelldusUnits.Id
        INNER JOIN TelldusActionTypes ON TelldusActions.FK_TelldusActionType_Id = TelldusActionTypes.Id
        INNER JOIN TelldusActionValues ON TelldusActions.FK_TelldusActionValue_Id = TelldusActionValues.Id
        INNER JOIN TelldusActionValueTypes ON TelldusActionValues.FK_TelldusActionValueType_Id = TelldusActionValueTypes.Id
        INNER JOIN TelldusActionsPerformed ON TelldusActionsPerformed.FK_TelldusAction_Id = TelldusActions.Id

    WHERE TelldusUnits.Name IN ("Grovkök golvtermostat", "Huvudtermostat", "Uterum golvtermostat")
        AND TelldusActionValueTypes.Name IN ("watt","temp")

    GROUP BY TelldusUnits.Name, TelldusActionValues.ActionValue, TelldusActionValueTypes.Name

    ORDER BY MAX(TelldusActionsPerformed.PerformedTime) DESC
) as OrderedResultSet

GROUP BY OrderedResultSet.TelldusUnits_Name, OrderedResultSet.TelldusActionValueTypes_Name

This gives me the top results (latest time):

Answer 2

You're Right, if you want the latest time, you'll need to add a GROUP BY clause not considering TelldusActionsPerformed.PerformedTime. Please try this script

SELECT
    --TelldusActionsPerformed.PerformedTime AS TelldusActionsPerformed_PerformedTime,
    MAX(TelldusActionsPerformed.PerformedTime) AS TelldusActionsPerformed_PerformedTime,
    TelldusUnits.Name AS TelldusUnits_Name,
    TelldusActionValues.ActionValue AS TelldusActionValues_ActionValue,
    TelldusActionValueTypes.Name AS TelldusActionValueTypes_Name
FROM
    TelldusActions
    INNER JOIN TelldusUnits ON TelldusActions.FK_TelldusUnit_Id = TelldusUnits.Id
    INNER JOIN TelldusActionTypes ON TelldusActions.FK_TelldusActionType_Id = TelldusActionTypes.Id
    INNER JOIN TelldusActionValues ON TelldusActions.FK_TelldusActionValue_Id = TelldusActionValues.Id
    INNER JOIN TelldusActionValueTypes ON TelldusActionValues.FK_TelldusActionValueType_Id = TelldusActionValueTypes.Id 
    INNER JOIN TelldusActionsPerformed ON TelldusActionsPerformed.FK_TelldusAction_Id = TelldusActions.Id
    WHERE TelldusUnits.Name IN ("Grovkök golvtermostat", "Huvudtermostat", "Uterum golvtermostat")
AND TelldusActionValueTypes.Name IN ("watt","temp")
GROUP BY TelldusUnits.Name,TelldusActionValues.ActionValue,TelldusActionValueTypes.Name
ORDER BY TelldusActionsPerformed.PerformedTime DESC