Sql Sort By Timestamp And Group By Id

I use php, mysql and html together to show a table. But I want to sort the items by timestamp and group the items with same id together. Example: Unsorted table: timestamp id

Solution 1:

If you're interested in an answer that uses both MySQL and PHP, then this will achieve what you want.

MySQL

SELECT `timestamp`, `id`, `name`, `description` FROM TableName ORDERBY `timestamp` DESC;

PHP

// execute your query and put the results into $dateSortedArray// I am assuming you know how to do this$outterArray = array();

// iterate through each row of the $dateSortedArrayforeach($dateSortedArrayas$row)
{
    // this is where we're doing the 'sub ordering' part// I'm pre-pending 'id_' in the index so that it is string-basedif(!isset($outterArray["id_".$row["id"]]))
    {
        // if we do not see this id in our result array yet,// add a new array with this row in it$outterArray["id_".$row["id"]] = array($row);

    }
    else
    {
        // if we have already see this id before,// add the current row to the array with this id-based index$outterArray["id_".$row["id"]][] = $row;
    }

}


// iterate through our result arrayforeach($outterArrayas$innerArray)
{
    foreach($innerArrayas$innerRow)
    {
        // simply dump out each line, comma-seperatedecho implode(",", $innerRow) . "\r\n";
    }
}

Working Example

Solution 2:

Try This:

Subquery will return you temp column having NewTimeStamp which will same for same id column value.

SELECTX.timestamp,
    X.Id,
    X.name,
    X.descriptionFROM
(
    SELECT TMain.timestamp,TMain.id,TMain.name,TMain.description
    (
       CASE WHEN ((SELECT COUNT(1) FROM @tblTest T WHERE T.Id=TMain.Id))>0 
       THEN (SELECT TOP(1) timestamp  FROM @tblTest T WHERE T.Id=TMain.Id ORDER BY timestamp) 
       ELSE timestamp END 
    ) AS NewTimeStamp FROM @tblTest TMain
)XORDERBYX.NewTimeStamp,X.timestamp