CODEWITHSUNDEEP
sqloracle-databasejoincompare
bcwan
bcwan

Reputation: 15

Comparing two tables that are the same and listing out the max date

I was wondering if it's possible to compare dates within the same table with same ID, but the catch is that there is an additional column that display the status. For instance, here's a table A:

Original Table

The results I would like to see is this:

Results

I know I could use a group by and max aggregate with ID to find the max date; however, I would like the status (Running/Stopped) column associated to be there. It would help me a lot.

Upvotes: 0

Views: 635

Answers (2)

Hilarion
Hilarion

Reputation: 870

In case of Oracle, you can use the KEEP clause like this:

SELECT t.id,
       MAX(t.status) KEEP (DENSE_RANK LAST ORDER BY t."DATE") AS corresponding_status,
       MAX(t."DATE") AS last_date
  FROM tab t
 GROUP BY t.id
 ORDER BY 1

For this sample data:

+----+---------+------------+
| ID | STATUS  | DATE       |
+----+---------+------------+
|  1 | Running | 2018-02-03 |
|  1 | Stopped | 2018-04-04 |
|  2 | Running | 2018-03-24 |
|  2 | Stopped | 2018-01-02 |
|  3 | Running | 2018-06-12 |
|  3 | Stopped | 2018-06-12 |
+----+---------+------------+

This would return this result:

+----+----------------------+------------+
| ID | CORRESPONDING_STATUS | LAST_DATE  |
+----+----------------------+------------+
|  1 | Stopped              | 2018-04-04 |
|  2 | Running              | 2018-03-24 |
|  3 | Stopped              | 2018-06-12 |
+----+----------------------+------------+

As can be seen in this SQL Fiddle.

For the cases, when you have multiple entries on the same ID and DATE combination, it'll choose one STATUS value - in this case the last one (based on alphanumerical sorting), as I've used MAX on the STATUS. The part LAST ORDER BY t."DATE" corresponds to how we choose DATE value in the group, i.e. by choosing the last DATE in the group.

See this Oracle Docs entry on more details.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269543

In most databases, the fastest method (assuming the right indexes) is a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.id = t.id);

Even if not the fastest, this should work in any database.

Upvotes: 2

Related Questions