-2

I am trying to get a list of records, grouped by client name within a given time period but am getting every single record rather than grouped records.

The data looks something like this:

ReqTS                      ClientName    ItemID    Outcome
2021-04-25 13:19:20:928    10.20.30.40   ABCD1     X0
2021-04-24 13:20:22:345    10.20.30.40   ABCD2     Y0
2021-04-26 13:21:35:456    10.20.30.40   ABCD3     X2
2021-04-25 13:18:45:589    10.20.40.50   ABCD4     Y1
2021-04-24 13:22:34:832    10.20.40.50   ABCD5     X0

I need to get:

2021-04-26 13:21:35:456    10.20.30.40   ABCD3     X2
2021-04-24 13:22:34:832    10.20.40.50   ABCD5     X0

I tried this but it returns all records:

select reqts, clientname,itemid, outcome
from reqresplog 
where logdate <= to_timestamp('04/26/2021', 'mm/dd/yyyy') and logdate >= to_timestamp('04/24/2021', 'mm/dd/yyyy')
group by clientname,reqts,itemid, outcome
order by reqts desc;
MT0
  • 143,790
  • 11
  • 59
  • 117
NoBullMan
  • 2,032
  • 5
  • 40
  • 93

1 Answers1

0

I'd think of

  • sorting rows by timestamp in descending order
  • within the same client name

(see the ROW_NUMBER analytic function), and then fetch row(s) sorted as the first in that group.

Your result looks wrong for the 2nd group; 25th of April is "later" than 24th.

Sample data till line #8; actual query begins at line #10.

SQL> with reqresplog (reqts, clientname, itemid, outcome) as
  2  (select
  3   to_timestamp('2021-04-25 13:19:20:928', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD1', 'X0' from dual union all select
  4   to_timestamp('2021-04-24 13:20:22:345', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD2', 'Y0' from dual union all select
  5   to_timestamp('2021-04-26 13:21:35:456', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.30.40', 'ABCD3', 'X2' from dual union all select
  6   to_timestamp('2021-04-25 13:18:45:589', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.40.50', 'ABCD4', 'Y1' from dual union all select
  7   to_timestamp('2021-04-24 13:22:34:832', 'yyyy-mm-dd hh24:mi:ss:ff3'), '10.20.40.50', 'ABCD5', 'X0' from dual
  8  )
  9  select reqts, clientname, itemid, outcome
 10  from (select r.*,
 11          row_number() over (partition by r.clientname order by r.reqts desc) rn
 12        from reqresplog r
 13       )
 14  where rn = 1;

REQTS                          CLIENTNAME  ITEMID OUTCOME
------------------------------ ----------- ------ ----------
26.04.21 13:21:35,456000000    10.20.30.40 ABCD3  X2
25.04.21 13:18:45,589000000    10.20.40.50 ABCD4  Y1

SQL>

If, for some reason, you still want the 24th of April, change analytic function's parameters. How? No idea, I don't see anything obvious.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Please don't post answers to questions that are obvious duplicates; instead please vote to close the question. – MT0 Apr 26 '21 at 19:54
  • @Littlefoot thank you for the answer. In hindsight,I should have searched SOF for similar questions before posting. Just got a little desperate I guess. Some of the links posted in this question does answer my question. I will try to check next time before posting. – NoBullMan Apr 27 '21 at 00:18
  • You're welcome. Note that it wasn't my objection to what you posted, but another person's - MT0. – Littlefoot Apr 27 '21 at 05:10