June 13, 2007 at 11:26 am
I have the data below in a table.
I need to pull out a distinct email address and the max(lastdate) per type.
select distinct(email), (select max(lastdate) from Xtable where type ='click') as LastClickDate,
(select max(lastdate) from Xtable where type ='open') as LastOpendate
from Xtable
But this only pull the lastest lastdate and not per email.
Do I have to loop it?
Email LastDate Type
e@y.com 2007-01-22 12:27:13.000 C
a@p.com 2007-01-22 12:27:38.000 C
fk@ce.net 2007-01-22 12:28:21.000 C
a@ya.com 2007-01-22 12:28:40.000 C
m@ya.com 2007-01-22 12:28:53.000 C
leo@se.com 2007-01-22 12:29:01.000 C
fk@l.net 2007-01-22 12:29:02.000 C
fk@l.net 2007-01-22 12:29:22.000 C
t@de.com 2007-01-22 12:29:52.000 C
b@kghf.com 2007-01-22 12:30:27.000 C
fk@ce.net 2007-03-12 12:07:42.000 O
fk@l.net 2007-03-12 12:00:14.000 O
b@kghf.com 2007-03-12 12:10:02.000 O
b@kghf.com 2007-03-12 12:00:07.000 O
b@kghf.com 2007-03-12 11:56:54.000 O
t@de.com 2007-03-12 12:04:38.000 O
leo@se.com 2007-03-12 12:01:30.000 O
t@de.com 2007-03-12 12:08:11.000 O
fk@l.net 2007-03-12 11:56:34.000 O
fk@ce.net 2007-03-12 11:59:12.000 O
June 13, 2007 at 11:28 am
select email, max(lastDate)
from XTable
GROUP by email
June 13, 2007 at 11:37 am
Sorry, I didn't inlcude the output I needed...
I need a max date per type per distinct email..
Email MaxLastDateClick MaxLastDateOpen
e@y.com 2007-01-22 2007-03-12
a@p.com 2007-01-22 2007-03-12
fk@ce.net 2007-01-22 2007-03-12
a@ya.com 2007-01-22 2007-03-12
m@ya.com 2007-01-22 2007-03-12
leo@se.com 2007-01-22
June 13, 2007 at 11:42 am
select
email,
max(case when type ='click' then lastdate else null end) as LastClickDate,
max(case when type ='open' then lastdate else null end) as LastOpendate
from Xtable
group by email
June 13, 2007 at 11:55 am
Perfect.
Thanks
Susan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply