How about trying the MIN or MAX functions, which will give you the first or
last ids
eg
SELECT u.Email,
MIN(u.id)
FROM user u
JOIN
(
SELECT email FROM user
GROUP BY Email
HAVING Count(*) > 1
) As u1 ON u.Email = u1.Email
GROUP BY u.Email
Jeremy
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 16 March 2003 00:19
> To: CF-Talk
> Subject: Re[2]: SQL first row of duplicates problem
>
>
> Hello Michael,
> thanks. Takes me
> in the right direction.
>
> I guess this takes THE First record
> of a query.
> But I would like to get EVERY First.
> Is there such a function ?
>
> Uwe
>
> Saturday, March 15, 2003, 11:36:20 PM, you wrote:
>
> MTT> SELECT TOP 1
>
>
> MTT> ----- Original Message -----
> MTT> From: <[EMAIL PROTECTED]>
> MTT> To: "CF-Talk" <[EMAIL PROTECTED]>
> MTT> Sent: Saturday, March 15, 2003 4:02 PM
> MTT> Subject: OT: SQL first row of duplicates problem
>
>
> >> Hi list,
> >> I have an SQL-problem
> >>
> >> 29 [EMAIL PROTECTED] Mister Mayer 2
> >> 26 [EMAIL PROTECTED] Mister Mayer 2
> >> 4 [EMAIL PROTECTED] Misses Miller 2
> >>
> >>
> >> I want to mark every first (but only the first !) line of the
> >> lines where the email adress is equal.
> >> e.g. with ID = 29 (see above)
> >>
> >> How can I do this ?
> >>
> >> SELECT email FROM user
> >> GROUP BY Email
> >> HAVING Count(*) > 1
> >>
> >> Gives me the email-record but
> >> I am not sure how to mark the FIRST
> >> duplicate row.
> >>
> >> Ideas ? Thanks !
> >>
> >> Uwe
> >>
> >>
> MTT>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
