|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
CAN'T SOLVE SELECT GROUPING WITH MAX FUNCTIONACCOUNT 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 select account, contact, date
from yourtable t1 where date = (select max(date) from yourtable where account=t1.account ) rlueneb***@gmail.com wrote: Show quote > 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 > 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 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 -- Show quote"rlueneb***@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 Show quote "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) t2 > ON t1.account=t2.account > > > -- > > > > "rlueneb***@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 > > > > 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 Show quote "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) 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) t2 > > ON t1.account=t2.account > > > > > > -- > > > > > > > > "rlueneb***@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 > > > > > > 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. -- Show quote"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) 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) 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) t2 > > > ON t1.account=t2.account > > > > > > > > > -- > > > > > > > > > > > > "rlueneb***@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 > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics