Last row for each day

[pic8picard]

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.

[tabreaz]

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


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

[ales]

Probably more performant query:
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

[cjard]

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


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:
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:
UPDATE tmp_del set action_date = action_date + dbms_random.value(0,1);


Here is the data:

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:


select
nvl2(ro_id, '', ''),
action_date,
quantity,
nvl2(ro_id, '', '')
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 :)

[ales]

:rolleyes:
ales query lacks the "within a date range" setting
... and ordering as well, you forgot to mention.

[pic8picard]

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 :)

[cjard]

:rolleyes:

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

Couldnt see ordering as a requirement! :D