CAN'T SOLVE SELECT GROUPING WITH MAX FUNCTION

Here is the scenario
ACCOUNT CONTACT DATE
account A CILXFA000HJ6 4/6/2005 16:05
account A C15467XXXXXX 7/27/2005 12:22
account B C20002XXXXXX 2/15/2005 18:25
account B C12225XXXXXX 12/14/2005 20:10
The RESULT should be this;
account A C15467XXXXXX 7/27/2005 12:22
account B C12225XXXXXX 12/14/2005 20:10
I tried using MAX function to get the LAST modified date display one
single record with account, contact and LAST modified date or last date
fields but the problem is that I can't use it to select out the right
contact because it is not designed to pick the last record? What are
other possible simple solutions. PS. The listing is already sorted by
date.
Rodselect account, contact, date
from yourtable t1
where date = (select max(date)
from yourtable
where account=t1.account
)
rlueneberg@.gmail.com wrote:
> Here is the scenario
> ACCOUNT CONTACT DATE
> account A CILXFA000HJ6 4/6/2005 16:05
> account A C15467XXXXXX 7/27/2005 12:22
> account B C20002XXXXXX 2/15/2005 18:25
> account B C12225XXXXXX 12/14/2005 20:10
> The RESULT should be this;
> account A C15467XXXXXX 7/27/2005 12:22
> account B C12225XXXXXX 12/14/2005 20:10
> I tried using MAX function to get the LAST modified date display one
> single record with account, contact and LAST modified date or last date
> fields but the problem is that I can't use it to select out the right
> contact because it is not designed to pick the last record? What are
> other possible simple solutions. PS. The listing is already sorted by
> date.
>
> Rod
>|||Alternatively:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.account=t2.account
"rlueneberg@.gmail.com" wrote:

> Here is the scenario
> ACCOUNT CONTACT DATE
> account A CILXFA000HJ6 4/6/2005 16:05
> account A C15467XXXXXX 7/27/2005 12:22
> account B C20002XXXXXX 2/15/2005 18:25
> account B C12225XXXXXX 12/14/2005 20:10
> The RESULT should be this;
> account A C15467XXXXXX 7/27/2005 12:22
> account B C12225XXXXXX 12/14/2005 20:10
> I tried using MAX function to get the LAST modified date display one
> single record with account, contact and LAST modified date or last date
> fields but the problem is that I can't use it to select out the right
> contact because it is not designed to pick the last record? What are
> other possible simple solutions. PS. The listing is already sorted by
> date.
>
> Rod
>|||Oops. This work better:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.date=t2.maxdate
"Mark Williams" wrote:
> Alternatively:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.account=t2.account
>
> --
>
> "rlueneberg@.gmail.com" wrote:
>|||Mark, you need to specify both columns in your join clause:
select t1.account, t1.contact, t2.maxdate from yourtable t1
INNER JOIN
(select account, MAX(date) as "maxdate" from yourtable group by account) t2
ON t1.account = t2.account and t1.date=t2.maxdate
Joe
"Mark Williams" wrote:
> Oops. This work better:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.date=t2.maxdate
>
> "Mark Williams" wrote:
>|||Thanks Joe. I thought about it for a few minutes, and realized that if two
accounts had the same max date value, my query would produce the wrong
results.
"Joe from WI" wrote:
> Mark, you need to specify both columns in your join clause:
> select t1.account, t1.contact, t2.maxdate from yourtable t1
> INNER JOIN
> (select account, MAX(date) as "maxdate" from yourtable group by account) t
2
> ON t1.account = t2.account and t1.date=t2.maxdate
> Joe
> "Mark Williams" wrote:
>|||That was exactly what I was looking. It took a quite some time to run
though, but it does the job precisely. I will test the other
suggestions and see how it goes.
Thank you
Rod|||Using the suggested script I found that there are some contacts with
exact same create date and time, including seconds. This is because
they were eletronically imported. This leads to another scenario. I
need to assign at least one contact as a primary contact. In this case,
how can I pick the first one or the last one? If this is not possible,
then how can I randomly pick just one record of each grouped result. PS
There is no autoincrement or sequential column.
For example:
account C CILXFA000HJ6 4/6/2005 16:05
account C CILXFA000333 4/6/2005 16:05
account D C20002XXXXXX 2/15/2005 18:25
account D C50902XXXXXX 2/15/2005 18:25
account D C50008XXXXXX 2/15/2005 18:25
Thanks
Rod