CODEWITHSUNDEEP
mysql
user785179
user785179

Reputation: 917

MySQL: Selecting MAX value and grouping

This is what I have now in my query:

+--------------------+-----------+------------+
| status             | entity_id | seconds    |
+--------------------+-----------+------------+
| Submitted          |       494 | 1352102400 |
| Accepted           |       494 | 1352275200 |
| In press/e-publish |       494 | 1352966400 |
| Rejected           |       520 | 1355817600 |
| Accepted           |       570 | 1352102400 |
+--------------------+-----------+------------+

I want it to look like:

+--------------------+-----------+------------+
| status             | entity_id | seconds    |
+--------------------+-----------+------------+
| In press/e-publish |       494 | 1352966400 |
| Rejected           |       520 | 1355817600 |
| Accepted           |       570 | 1352102400 |
+--------------------+-----------+------------+

In quasi-SQL:

SELECT status, entity_id, MAX(seconds) 
FROM foo
GROUP BY entity_id, seconds

The above quasi-SQL looks correct, but the "status" column value does not correspond to the correct row. I get back something like the below:

+--------------------+-----------+------------+
| status             | entity_id | seconds    |
+--------------------+-----------+------------+
| Submitted          |       494 | 1352966400 |
| Rejected           |       520 | 1355817600 |
| Accepted           |       570 | 1352102400 |
+--------------------+-----------+------------+

Upvotes: 1

Views: 10540

Answers (2)

Dhinakar
Dhinakar

Reputation: 4151

The below Query will give your expected Result.

SELECT max(maxsec), status, entity_id from 
(SELECT status, entity_id, MAX(seconds)  as maxsec
FROM table1
GROUP BY entity_id,status) a GROUP BY entity_id

Your schema created here.

Upvotes: 3

Amadan
Amadan

Reputation: 198314

Untested, but should look something like this:

SELECT status, entity_id, seconds
FROM entities E
WHERE E.seconds == (
  SELECT MAX(E2.seconds)
  FROM entities E2
  WHERE E2.entity_id = E.entity_id
)

(Setting up a SQLfiddle for your question would give you a more tested answer :p )

Upvotes: 6

Related Questions