Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
finding the min date of different dataset and pivoting

Hi everyone,
i have a scenario where i want to find the min dates from multiple tables that contain different date field. let me explain further. please consider the following data
with summ as
(
SELECT 8521 AS cid, 'partial' as status, to_date('2/16/2015 8:54:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'partial' as status,to_date('2/15/2015 5:28:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 7364 AS cid, 'complete' as status ,to_date('2/16/2015 2:15:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'complete' as status ,to_date('2/15/2015 8:03:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 6361 AS cid, 'complete' as status ,to_date('2/15/2015 8:17:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'partial' as status , to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual
)
,orders as
(
SELECT 8521 AS cid, 'attending' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8521 AS cid, 'phone' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8521 AS cid, 'md' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'attending' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'phone' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'md' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'attending' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'phone' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'md' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'attending' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'phone' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'md' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual
)
,detail as
(
SELECT 8521 AS cid, 'date_Read' as data_val, to_date('3/17/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 1428 AS cid, 'date_Read' as data_val, to_date('2/24/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 8965 AS cid, 'date_Read' as data_val, to_date('3/31/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 6935 AS cid, 'date_Read' as data_val, to_date('2/25/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual
)
i want to join all 3 tables (summ, orders,detail) by cid, v_id and i want to find the following.
from summ table i want to find the row with min ev_dt where status is complete. from orders table i want to find the row with min or_dt
and from detail table i want to find out the row with min date_Read. my output should look like this
vi_id first_complete first_ordered first_read
123 2/15/2015 8:03:00 PM 2/15/2015 4:59:00 PM 2/24/2015 4:59:00 PM
my query looks like this but it is not completed
select * from (
SELECT row_number () over (partition by v_id , status order by ev_dt) as first_completed,
row_number () over (partition by v_id order by ord_dt) as first_ordered,
row_number () over (partition by v_id order by date_Read) as first_read
FROM summ a
JOIN orders b
on (a.v_id = b._vid)
JOIN detail c
on (a.v_id = c.v_id)
) where first_completed = 1 or (first_ordered = 1 and status='complete')
or first_read = 1
can someone help me modify my query so that i can get the output describe above? im using oracle 11g
thanks in advance
Answers
-
First you must make up your mind. You said this:
i want to join all 3 tables (summ, orders,detail) by cid, v_id and i want to find the following.
but then everything else in your post joins by v_id only. So, which is it? Below I will assume that you are only joining by V_ID. If you need to join on both columns, it's an easy modification to the query below.
Then: You are going about it the wrong way from the start. You should aggregate each table separately first, and then join the results (which will be much smaller than the original tables). Don't join first - there is a lot of data in the base tables that is not needed in the end result, so don't join unneeded rows from one table to rows from the other two. And, standard aggregation suffices - you don't need analytic functions for this task.
Here it is:
select v_id, s.first_complete, o.first_ordered, d.first_readfrom (select v_id, min(ev_dt) as first_complete from summ where status = 'complete' group by v_id) s full outer join (select v_id, min(or_dt) as first_ordered from orders group by v_id) o using (v_id) full outer join (select v_id, min(date_read) as first_read from detail group by v_id) d using (v_id); V_ID FIRST_COMPLETE FIRST_ORDERED FIRST_READ ---------- -------------------- -------------------- -------------------- 123 2/15/2015 8:03:00 PM 2/15/2015 4:59:00 PM 2/24/2015 4:59:00 PM
-
Something along the lines.
You mentioned that tables are joined on cid and v_id.
But if i join them on cid and v_id then the dates you mention are for cid 1428 which is partial.
Please advise if the data is correct.
So i have removed the join for cid.
Alvinder
with summ as(SELECT 8521 AS cid, 'partial' as status, to_date('2/16/2015 8:54:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union allSELECT 1428 AS cid, 'partial' as status,to_date('2/15/2015 5:28:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union allSELECT 7364 AS cid, 'complete' as status ,to_date('2/16/2015 2:15:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union allSELECT 8965 AS cid, 'complete' as status ,to_date('2/15/2015 8:03:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union allSELECT 6361 AS cid, 'complete' as status ,to_date('2/15/2015 8:17:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union allSELECT 6935 AS cid, 'partial' as status , to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual),orders as(SELECT 8521 AS cid, 'attending' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 8521 AS cid, 'phone' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 8521 AS cid, 'md' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 1428 AS cid, 'attending' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 1428 AS cid, 'phone' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 1428 AS cid, 'md' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 8965 AS cid, 'attending' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 8965 AS cid, 'phone' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 8965 AS cid, 'md' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 6935 AS cid, 'attending' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 6935 AS cid, 'phone' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union allSELECT 6935 AS cid, 'md' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual),detail as( SELECT 8521 AS cid, 'date_Read' as data_val, to_date('3/17/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union allSELECT 1428 AS cid, 'date_Read' as data_val, to_date('2/24/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union allSELECT 8965 AS cid, 'date_Read' as data_val, to_date('3/31/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union allSELECT 6935 AS cid, 'date_Read' as data_val, to_date('2/25/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual)SELECT distinct a.v_id, MIN(ev_dt) first_Complete_dt, -- a.cid, FIRST_VALUE(or_dt) OVER(PARTITION BY a.v_id ORDER BY or_dt ) first_ordered_dt, FIRST_VALUE(date_read) OVER(PARTITION BY a.v_id ORDER BY or_dt ) First_Date_Rd --MIN(date_read),FROM summ a, orders b, detail cWHERE a.v_id = b.v_id AND a.v_id = c.v_id -- AND a.cid = b.cid--and a.cid= c.cidand a.status='complete'group by a.v_id --, a.cid , or_dt,date_read;
-
thank you for your replies. my apologies for the confusion. yes, the join is on cid and v_id. in my query i forgot to add cid on the join. in your query, should i just add the join with cid or it will required further modification. thanks
-
You didn't just forget to add cid to the joins - you didn't include it in the "desired output" either. At the very least, the output should have a column for CID, if you group and join on it, right?
This is where the FULL OUTER joins in my reply become even more important (depending, though, on your complete and detailed problem statement). In some cases, for some CID, you only have data in one or two of the three tables - not in all three of them. If you need to show all available data for ALL pairs (CID, V_ID) - even when data is missing from one of the tables - then you need FULL OUTER JOIN as I have written it. (And that's also when the USING clause is more convenient than an ON clause for the join.) On the other hand, if you only need the output for (CID, V_ID) that have at least one row (with 'complete' status in the first table), then you will need to change the joins to INNER JOIN.
Here is the modified query and the full output with the requested change. Note that you need to add CID to the earlier query in several places: the SELECT and GROUP BY clauses of every subquery, to the USING clause of the joins, and also to the outer SELECT (main query).
select cid, v_id, s.first_complete, o.first_ordered, d.first_readfrom (select cid, v_id, min(ev_dt) as first_complete from summ where status = 'complete' group by cid, v_id) s full outer join (select cid, v_id, min(or_dt) as first_ordered from orders group by cid, v_id) o using (cid, v_id) full outer join (select cid, v_id, min(date_read) as first_read from detail group by cid, v_id) d using (cid, v_id); CID V_ID FIRST_COMPLETE FIRST_ORDERED FIRST_READ ---------- ---------- -------------------- -------------------- ----------------------- 8521 123 2/16/2015 8:17:00 AM 3/17/2015 8:17:00 AM 1428 123 2/15/2015 4:59:00 PM 2/24/2015 4:59:00 PM 8965 123 2/15/2015 8:03:00 PM 2/15/2015 8:01:00 PM 3/31/2015 8:01:00 PM 6935 123 2/16/2015 1:57:00 AM 2/25/2015 1:57:00 AM 6361 123 2/15/2015 8:17:00 PM 7364 123 2/16/2015 2:15:00 AM
If you use INNER JOIN instead of FULL OUTER JOIN everywhere, you will only get the row for CID = 8965.
-
thanks again. actually, i dont want to display the cid in the output because i will get multiple rows and i am looking for one row only which has all 3 dates. that is the reason i was using analytics function instead of min aggregation. i was thinking of joining all the tables together with cid and visitid and then do analytics function to get one row with the min dates from each table and acquire the output i mentioned above
-
The issue is if you join on CID as well.
then the output you are expecting is for CID=1428 and the status is "Partial" for that. Not "Complete. Maybe i am missing something.
As @mathguy mentioned you will get the rows which is displayed in bold in the output.
Alvinder
-
I think the confusion here is that you are relating the dates to specific cid. to clarify futher, this is what i am looking for.
i want to join all 3 tables by cid, v_id. from that result set then i want to find the min date(ev_dt) where status is complete. then from the same result set (3 tables joined together) i want the row with min or_dt . then from same result set, i want to find min date_read.
thats is the reason i join all 3 tables first and then from the result set i was trying to find the min dates using analytics functions
if you use min and group by on each table separate, then i guess it is more difficult. let me now if this is clear now. thanks
-
Please explain what you believe it means to “join on cid and v_id“, since obviously you are not using the technical term. I have no idea what you mean.
Good luck with your task.