Solved

selecting duplicates AND non duplicates.

Posted on 2007-04-11
3
187 Views
Last Modified: 2007-04-11
I am doing some maintenance on our email user database.  I have the following statement that selects the latest email from the duplicate emails.

SELECT
      EmailAddress,
      MAX(USERID) AS LatestUserID
FROM
      Account
GROUP BY
      EmailAddress
HAVING
      Count(EmailAddress) > 1

What I want to do, is in the SAME statement, select all the emails that are not duplicates. So I have a unique email address on each row.
Thanks in advance
0
Comment
Question by:rbradberry
  • 2
3 Comments
 
LVL 10

Expert Comment

by:ksaul
ID: 18891723
SELECT
      EmailAddress,
      MAX(USERID) AS LatestUserID
FROM
      Account
GROUP BY
      EmailAddress
HAVING
      Count(EmailAddress) > 1

UNION

SELECT
      EmailAddress,
      MAX(USERID)
FROM
      Account
GROUP BY
      EmailAddress
HAVING
      Count(EmailAddress) = 1
0
 
LVL 10

Accepted Solution

by:
ksaul earned 500 total points
ID: 18891739
Actually, if I understand it right you can just do:

SELECT
      EmailAddress,
      MAX(USERID) AS LatestUserID
FROM
      Account
GROUP BY
      EmailAddress
0
 
LVL 3

Author Comment

by:rbradberry
ID: 18891900
wow, that seems to have worked out perfectly
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Read about achieving the basic levels of HRIS security in the workplace.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question