How to use Having with Group By and Order By in Oracle?

Question

SELCT * FROM MyTable;

ID    Status            posted_date       posted_by
---------------------------------------------------
0      invalid          01/01/2021         abc
1      in-progress      02/01/2021         xyz
0      invalid          03/01/2021         lmn
2      complete         04/01/2021         pqr
1      in-progress      05/01/2021         newton
2      complete         06/01/2021         einstein
2      complete         07/01/2021         jack

I need to group by IDs. Then order by the posted_date descending. Then find out the user who posted the latest transaction.

In this case, my expected output is,

ID    Status            posted_date       posted_by
---------------------------------------------------
2      complete          07/01/2021        jack
1      in-progress       05/01/2021        newton
0      invalid           03/01/2021        lmn

Below is what I tried. I am not getting any rows.

SELECT COUNT(ID), ID, status, posted_by,posted_date
FROM MyTable
GROUP BY ID, status, posted_by,posted_date
HAVING COUNT(ID) > 1
ORDER BY posted_date DESC;

Answer 1

Your problem is getting the row with max date, you have several ways to get it

SELECT ID, status, posted_by, posted_date
FROM MyTable
WHERE (id, posted_date) IN 
    (
        SELECT id, MAX(posted_date)
        FROM MyTable
        GROUP BY id
    )
ORDER BY id;

Using JOIN

SELECT t.ID, t.status, t.posted_by, t.posted_date
FROM MyTable t
INNER JOIN 
(
    SELECT id, MAX(posted_date) AS max_posted_date
    FROM MyTable
    GROUP BY id
) m
ON t.id = m.id AND t.posted_date = m.max_posted_date
ORDER BY t.id;

Or using analytic function

SELECT *
FROM 
(
    SELECT ID, status, posted_by, posted_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER by posted_date DESC) AS rn
    FROM MyTable
)
WHERE rn = 1 
ORDER BY id;