max date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

max date

Author

Topic

akas
Starting Member

42 Posts

Posted - 2008-07-22 : 11:45:05

SELECT contact_id, max(contact_date), contact
FROM vwcontactlog
group by contact_id, contact

contact_id, cotact_date contact
-----------------------------------
1 10/02/2007
1 10/02/2007 GC/SG/Sherry Mulligan
1 08/13/2007 PEK and KN--Meeting Shannon Oates
3 10/18/2007 Brian C.
5 01/30/2008 AP
5 10/02/2007 EO coordinated with Jennifer Stawarz
7 07/30/2007 Conf. Call - KN, AP, PH, AIR & Levine
7 07/11/2007 Email - John Walsh
7 07/18/2007 Email - John Walsh - Lara Crissey
7 01/01/2008 EO/KN/Ifdy
7 01/01/2008 JW-PEK
7 08/09/2007 OK from John Walsh
7 04/02/2008 test

i m still getting wrong results..
i just want max date row..

contact_id, max(cotact_date) contact
-----------------------------------

1 10/02/2007 GC/SG/Sherry Mulligan
3 10/18/2007 Brian C.
5 01/30/2008 AP
7 04/02/2008 test

how can i get this results..

can anyone help me.

thanks

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-22 : 12:12:19

It seems like its duplicating the date when the contact is different. Have you tried to max(contact)?

quote:
Originally posted by akas

SELECT contact_id, max(contact_date), contact
FROM vwcontactlog
group by contact_id, contact

contact_id, cotact_date contact
-----------------------------------
1 10/02/2007
1 10/02/2007 GC/SG/Sherry Mulligan
1 08/13/2007 PEK and KN--Meeting Shannon Oates
3 10/18/2007 Brian C.
5 01/30/2008 AP
5 10/02/2007 EO coordinated with Jennifer Stawarz
7 07/30/2007 Conf. Call - KN, AP, PH, AIR & Levine
7 07/11/2007 Email - John Walsh
7 07/18/2007 Email - John Walsh - Lara Crissey
7 01/01/2008 EO/KN/Ifdy
7 01/01/2008 JW-PEK
7 08/09/2007 OK from John Walsh
7 04/02/2008 test

i m still getting wrong results..
i just want max date row..

contact_id, max(cotact_date) contact
-----------------------------------

1 10/02/2007 GC/SG/Sherry Mulligan
3 10/18/2007 Brian C.
5 01/30/2008 AP
7 04/02/2008 test

how can i get this results..

can anyone help me.

thanks

Thanks in Advance!
Sherri

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-22 : 12:33:27

[code]; with a as (select *,
row_number() over (partition by contact_id order by contact_date desc, contact desc) as row
from vwcontactlog)
select contact_id, contact_date, contact from a where row = 1[/code]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-22 : 13:20:39

[code]select t.*
from yourtable t
inner join (select contact_id,max(contact_date) as maxdate
from yourtable
group by contact_id) tmp
on tmp.contact_id=t.contact_id
and tmp.maxdate=t.contact_date[/code]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-22 : 13:28:59

As long as we're throwing out alternatives:


SELECT cl.contact_id, ca.contact_date, ca.contact
FROM vwcontactlog cl
cross apply (select top 1 z.conctact_date, z.contact 
              from vwcontactlog z
              where z.contactid = cl.contact_id 
              order by z.conctact_date desc) ca
group by cl.contact_id, ca.contact_date, ca.contact

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources