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, '[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

[ales]

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]

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

Couldnt see ordering as a requirement!