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;

Skip the HAVING clause for a while and take a closer look at the GROUP BY result. — jarlh, Oct 12 '21 at 08:38

score 2 · Accepted Answer · answered Oct 12 '21 at 08:59

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;

score 1 · Answer 2 · answered Oct 12 '21 at 08:59

It doesn't look like you want to use an aggregate function. It looks like you want to use a window function. Something like this (use dense_rank or row_number rather than rank depending on how you want to handle ties)

with ranked_data as (
  select t.*,
         rank() over (partition by id 
                          order by posted_date desc) rnk
    from yourTable t
)
select *
  from ranked_data
 where rnk = 1

score 1 · Answer 3 · answered Oct 12 '21 at 09:22

Schema and insert statements:

 create table MyTable(ID int, Status varchar(100), posted_date date, posted_by varchar(50));

 insert into MyTable values(0, 'invalid',          date'2021-01-01','abc');
 insert into MyTable values(1, 'in-progress',      date'2021-01-02','xyz');
 insert into MyTable values(0, 'invalid',          date'2021-01-03','lmn');
 insert into MyTable values(2, 'complete',         date'2021-01-04','pqr');
 insert into MyTable values(1, 'in-progress',      date'2021-01-05','newton');
 insert into MyTable values(2, 'complete' ,        date'2021-01-06','einstein');
 insert into MyTable values(2, 'complete',         date'2021-01-07','jack');

Query:

 with cte as
 (
   select ID,Status,posted_date,posted_by,
   row_number()over(partition by id order by posted_date desc) as rn
   from MyTable
 )
 select ID,Status,posted_date,posted_by from cte where rn=1 
 order by posted_date desc

Output:

ID	STATUS	POSTED_DATE	POSTED_BY
2	complete	07-JAN-21	jack
1	in-progress	05-JAN-21	newton
0	invalid	03-JAN-21	lmn

db<>fiddle here

score 0 · Answer 4 · answered Oct 12 '21 at 09:21

0

You can also use keep dense_rank aggregate clause to do that.

select ID
, max(STATUS)keep(dense_rank first order by POSTED_DATE desc) STATUS
, max(POSTED_DATE)keep(dense_rank first order by POSTED_DATE desc)POSTED_DATE
, max(POSTED_BY)keep(dense_rank first order by POSTED_DATE desc)POSTED_BY
from myTable t
group by ID
order by ID desc
;

demo

answered Oct 12 '21 at 09:21

Mahamoutou

1,555
1
5
11

You have to be very careful with this solution. If there can be 2 rows with the same latest date for an id then you may get the status from one row and the user from the other row. [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=9cfd197ad1a46b172e1e985aec21c373) – MT0 Oct 12 '21 at 10:06

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

4 Answers4