CODEWITHSUNDEEP
mysqlgroup-by
ina
ina

Reputation: 19534

MySQL - Group by with Order by DESC

table: uuid, version, datetime

version is not unique, but the idea is to fetch only the rows with the latest datetime for a given uuid

SELECT * FROM table WHERE uuid='bla' GROUP BY version ORDER BY datetime desc

... of course gets datetime asc results -- is there a way to "preorder" the group by to desc, so that only the latest version is fetched?

Upvotes: 8

Views: 35540

Answers (4)

Dinesh M
Dinesh M

Reputation: 1

I used a separate method on php to get the id's and put it into an array. Then on another sql query i searched the data by looking up the id's

$ids  = array();

$sql = "SELECT pid FROM table_name  WHERE DATE(column_name)='yyyy-mm-dd'  ORDER BY column_name ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

if (!in_array($row['value'], $ids)) {
    array_push($ids, $row['value']);
}
    }
}

And then

$sql = "SELECT * FROM table_name WHERE id IN (".implode(',', $ids).") ORDER BY column_name ASC";

Upvotes: 0

azerafati
azerafati

Reputation: 18583

There is a better way for me, you could add a desc to group by:

SELECT * FROM table WHERE uuid='bla' GROUP BY version desc

why it works is because my id's are generated and so always the latest id means the latest datetime

Upvotes: 2

bryan
bryan

Reputation: 71

SELECT * FROM 
(SELECT * FROM table WHERE uuid='bla' ORDER BY datetime desc) table 
GROUP BY version;

Upvotes: 6

The Scrum Meister
The Scrum Meister

Reputation: 30111

since the table only has those 3 field, and you are filtering by uid you can just use the MAX without the JOIN:

SELECT version, MAX(datetime) Maxdatetime
FROM table
WHERE uuid='bla'
GROUP BY version

However, if the table had more fields, or you are not filtering by uid - you need to first get the MAX datetime for each version, then select the row:

SELECT t.uuid, t.version, t.datetime 
FROM table t JOIN (
    SELECT version, MAX(datetime) Maxdatetime
    FROM table
    WHERE uuid='bla'
    GROUP BY version
) r ON t.version = r.version AND t.datetime = r.Maxdatetime
WHERE t.uuid='bla'
ORDER BY t.datetime desc

Upvotes: 26

Related Questions