Last row for each day

I have what is probably an easy question for most but searching for this
has turned into a nightmare.

Basically, I have a table as follows (many columns omitted):

ACTION_DATE QUANTITY
11/9/2004 9:08:20 AM 0
11/9/2004 10:23:40 AM 0
11/9/2004 10:23:41 AM 2
11/23/2004 11:20:20 AM 1
11/23/2004 11:20:24 AM 1
11/23/2004 11:20:25 AM 4
11/23/2004 11:20:25 AM 5

What I need to be able to do is select the quantity from last row
for each day given a date range.

Any help would be great.
Thanks.

Try this code, the problem with this query is, it scans the table action_quantities twice.

Code:

---

SELECT *
FROM action_quantities a,
(
  SELECT MAX(Action_Date) act_dt
  FROM action_quantities a,
  (
    SELECT
        to_date('&d1', 'DD-MON-YYYY') + (rownum - 1) dt
    FROM        dual
    CONNECT BY LEVEL < (to_date('&d2', 'DD-MON-YYYY') -
                                        to_date('&d1', 'DD-MON-YYYY')+1)
  ) b
WHERE trunc(a.action_date) = b.dt
GROUP BY trunc(action_date)
)b
WHERE a.action_date = b.act_dt

---

Probably more performant query:

Code:

---

select action_date, quantity
from
(
  select
          trunc(action_date) as action_date,
          quantity,
          row_number()
                  over
                  (
                          partition by trunc(action_date)
                          order by action_date desc
                  )
                  as rownumber
  from action_quantity
)
where rownumber=1

---

ales query lacks the "within a date range" setting..

Code:

---

select
  trunc(action_date) as the_day,
  quantity as last_qty
from
  tmp_del td
  inner join
  (
    select
      rowid as ro_id,
      row_number() over (partition by trunc(action_date) order by action_date desc) as grp_id
    from
      tmp_del
    where
      action_date >= to_date('20041107', 'YYYYMMDD') and
      action_date < to_date('20041121', 'YYYYMMDD')
  ) finder
  on
    finder.ro_id = td.rowid and
    finder.grp_id = 1

---

note you didnt say what your table was called.. I called mine tmp_del
A search/repl should fix that

I filled the table with:

Code:

---

insert into tmp_del values(replace('11/{}/2004 00:00:00', '{}', trunc(dbms_random.value(1,29))), trunc(dbms_random.value(1,10)));

---

Set the times with:

Code:

---

UPDATE tmp_del set action_date = action_date + dbms_random.value(0,1);

---

Here is the data:

Code:

---

ACTION_DATE        QUANTITY
        11/01/2004 09:03:57        1       
        11/01/2004 16:08:34        2       
        11/02/2004 14:23:40        3       
        11/02/2004 23:24:51        5       
        11/05/2004 06:30:00        6       
        11/06/2004 03:06:20        1       
        11/06/2004 15:56:08        1       
        11/06/2004 21:39:06        5       
        11/06/2004 22:10:13        9       
        11/06/2004 22:59:05        4       
        11/07/2004 12:11:22        9       
        11/07/2004 17:27:18        3       
        11/08/2004 04:52:03        4       
        11/08/2004 11:10:00        9       
        11/08/2004 14:39:13        9       
        11/09/2004 12:20:21        0       
        11/09/2004 13:59:34        4       
        11/09/2004 21:26:27        9       
        11/10/2004 12:49:38        0       
        11/10/2004 19:21:42        8       
        11/10/2004 21:23:58        5       
        11/11/2004 18:20:06        8       
        11/11/2004 20:50:34        0       
        11/12/2004 00:20:36        5       
        11/13/2004 09:42:54        2       
        11/13/2004 10:09:20        1       
        11/13/2004 10:10:35        1       
        11/13/2004 20:24:54        3       
        11/14/2004 04:47:26        8       
        11/14/2004 08:25:40        3       
        11/14/2004 16:06:13        3       
        11/14/2004 23:11:40        4       
        11/15/2004 16:29:18        1       
        11/15/2004 16:53:26        9       
        11/15/2004 17:49:46        7       
        11/16/2004 09:53:32        5       
        11/16/2004 13:50:19        9       
        11/16/2004 14:07:27        2       
        11/16/2004 18:33:30        2       
        11/16/2004 22:37:44        8       
        11/17/2004 04:28:32        8       
        11/17/2004 20:23:57        7       
        11/18/2004 18:32:48        6       
        11/18/2004 23:23:03        5       
        11/19/2004 11:00:18        4       
        11/19/2004 13:57:53        9       
        11/19/2004 22:01:45        3       
        11/20/2004 02:57:41        3       
        11/20/2004 03:27:57        7       
        11/20/2004 22:27:06        7       
        11/21/2004 01:24:12        5       
        11/21/2004 03:33:15        4       
        11/21/2004 04:14:28        8       
        11/21/2004 05:31:21        1       
        11/21/2004 12:55:39        6       
        11/22/2004 07:55:46        6       
        11/23/2004 03:14:26        5       
        11/23/2004 04:51:08        6       
        11/24/2004 03:10:13        2       
        11/24/2004 20:17:55        6       
        11/25/2004 02:43:31        8       
        11/25/2004 05:51:56        8       
        11/27/2004 16:11:26        5       
        11/28/2004 01:37:56        2       
        11/28/2004 10:34:57        8       
        11/28/2004 14:07:16        2

---

To check the query i changed it:

Code:

---

select
  nvl2(ro_id, '[b]', ''),
  action_date,
  quantity,
  nvl2(ro_id, '[/b]', '')
from
  tmp_del td
  left outer join
  (
    select
      rowid as ro_id,
      row_number() over (partition by trunc(action_date) order by action_date desc) as grp_id
    from
      tmp_del
    where
      action_date >= to_date('20041107', 'YYYYMMDD') and
      action_date < to_date('20041121', 'YYYYMMDD')
  ) finder
  on
    finder.ro_id = td.rowid and
    finder.grp_id = 1
   
order by action_date

---

I used [b] tags to highlight the last row in my query grid because I knew I could also paste this into vBulletin and the rows would bold :)

:rolleyes:

Quote:

---

ales query lacks the "within a date range" setting

---

... and ordering as well, you forgot to mention.

You guys are the best. I was just looking for how to do the sub-query and
you all went the extra step to literally let me cut-paste/search-replace
it to have it work.

Thanks again.
Paul :)

Quote:

---

Originally Posted by ales

:rolleyes:

... and ordering as well, you forgot to mention.

---

Couldnt see ordering as a requirement! :D