Min / max function

[Anonymous]

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

[Anonymous]

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.

[jonathandienst]

Hi

Have a look in the manual at FirstSortedValue()

Regards

Jonathan

[#4]

AGGRTABLE:

LOAD CONTACTID,

MAX(ACCOUNTID) AS ACCOUNTID,

MAX(DATESCHEDULED) AS DATESCHEDULED;

RESIDENT MAINDATA

GROUP BY CONTACTID;

[#5 · Accepted solution]

LOAD ACCOUNTID,

     max(date(DATESCHEDULED)) as DATESCHEDULED

FROM .......group by ACCOUNTID;

left join load CONTACTID,

     ACCOUNTID,

     DATESCHEDULED

FROM ......;

[nstefaniuk]

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.

[Anonymous]

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

[nstefaniuk]

Take care that if you have 2 CONTACTID on same date, you will have duplicates...

[Anonymous]

nstefaniuk, thanks. I assume that's only the case for 2 CONTACTID's on the same date for one specific customer, isn't it?

[nstefaniuk]

Yes