Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I think this is a question which can be solved with a min/max function, but I can't get it done. I hope one of you guys can help me out.
I do have the following data:
CONTACTID | ACCOUNTID | DATESCHEDULED |
---|---|---|
10 | 150 | 02-05-2012 |
12 | 150 | 06-07-2012 |
15 | 150 | 05-06-2012 |
20 | 150 | 03-04-2012 |
50 | 160 | 05-02-2012 |
55 | 160 | 05-07-2012 |
65 | 160 | 16-04-2012 |
Now I would like to see the contactids per accountid which have the highest date. The result should be like this:
CONTACTID | ACCOUNTID | DATESCHEDULED |
---|---|---|
12 | 150 | 06-07-2012 |
55 | 160 | 05-07-2012 |
As you can see, there is no logic between the contactids and the datescheduled.
I am using Oracle SQL, but solutions in another way are also welcome.
Regards,
Henco
LOAD ACCOUNTID,
max(date(DATESCHEDULED)) as DATESCHEDULED
FROM .......group by ACCOUNTID;
left join load CONTACTID,
ACCOUNTID,
DATESCHEDULED
FROM ......;
EDIT: of course I can get this result in QV itself by using max(datescheduled) as expression and accountid as dimension, but I would like to do this in the script. Hope one of you can help me out.
Hi
Have a look in the manual at FirstSortedValue()
Regards
Jonathan
AGGRTABLE:
LOAD CONTACTID,
MAX(ACCOUNTID) AS ACCOUNTID,
MAX(DATESCHEDULED) AS DATESCHEDULED;
RESIDENT MAINDATA
GROUP BY CONTACTID;
LOAD ACCOUNTID,
max(date(DATESCHEDULED)) as DATESCHEDULED
FROM .......group by ACCOUNTID;
left join load CONTACTID,
ACCOUNTID,
DATESCHEDULED
FROM ......;
In Oracle:
select ACCOUNTID, min(CONTACTID) keep (dense_rank first order by DATESCHEDULED desc) FROM MYTABLE GROUP BY ACCOUNTID ORDER BY ACCOUNTID;
or
select ACCOUNTID, min(CONTACTID) keep (dense_rank last order by DATESCHEDULED asc) FROM MYTABLE GROUP BY ACCOUNTID ORDER BY ACCOUNTID;
the min(CONTACTID) is used if you have 2 CONTACTID on the same ACCOUNTID with the same highest DATESCHEDULED.
Everyone who reacted here, many thanks. It works fine! As I can see there are many ways to accomplish this, I choose the way Pari Pari told me to do it.
Thanks again.
Cheers,
Henco
Take care that if you have 2 CONTACTID on same date, you will have duplicates...
nstefaniuk, thanks. I assume that's only the case for 2 CONTACTID's on the same date for one specific customer, isn't it?
Yes