get the list of records for last registered emails

hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanksTry:
select
count (*)
, email
, max (regdatetime) regdatetime
group by
email
having
count (*) > 1
order by
regdatetime desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanks|||Tom it works perfect ...
thanks for your help
"Tom Moreau" wrote:

> Try:
> select
> count (*)
> , email
> , max (regdatetime) regdatetime
> group by
> email
> having
> count (*) > 1
> order by
> regdatetime desc
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
> hello,
> i have a table for example mytable with 2 fields
> email (varchar50) regdate(datetime)
> i want to have a list of emails which are more times registered - sort by
> last time when registered
>
> example of entries in the table
> u1@.dom1.com 26.03.2006 15:12:02
> u2@.dom1.com 24.03.2006 15:12:02
> u3@.dom1.com 24.03.2006 14:12:02
> u1@.dom1.com 23.03.2006 13:12:02
> u2@.dom1.com 22.03.2006 12:12:02
> u1@.dom1.com 21.03.2006 11:12:02
> u2@.dom1.com 20.03.2006 12:12:02
> u2@.dom1.com 19.03.2006 12:12:02
>
> i want to get something like
> 3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
> time
> 4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
> u3 - is not listed because it is only one time registered
>
> the information is sort desc by last registration time
> Yes i know what you think about the "tabledesign..." but my customer has
> such a table - and he asked me for that information:The information what
> will result is then inserted in a new table...
> thanks
>