SQL - Removing Duplicate without 'hard' coding?

Question

Heres my scenario.

I have a table with 3 rows I want to return within a stored procedure, rows are email, name and id. id must = 3 or 4 and email must only be per user as some have multiple entries.

I have a Select statement as follows

SELECT
     DISTINCT email,
     name,
     id
from table
where
     id = 3
     or id = 4

Ok fairly simple but there are some users whose have entries that are both 3 and 4 so they appear twice, if they appear twice I want only those with ids of 4 remaining. I'll give another example below as its hard to explain.

Table -

Email              Name    Id
jimmy@domain.com   jimmy   4
brian@domain.com   brian   4
kevin@domain.com   kevin   3
jimmy@domain.com   jimmy   3

So in the above scenario I would want to ignore the jimmy with the id of 3, any way of doing this without hard coding?

Thanks

Which RDBMS are you using (MySQL, Oracle etc...)? Please _always_ tag SQL questions with the tag as there may be some additional feature of the RDBMS that you are using that can help. — Ben, Jul 05 '12 at 20:48
Sorry its SQL Server so T-SQL. Thanks for all the responses. Think the Max(id) should do the trick, much appreciated — DtotheG, Jul 05 '12 at 21:41

Randy · Accepted Answer · 2012-07-05T22:43:24.237

2

SELECT
     email,
     name,
     max(id)
from table
where
     id in( 3, 4 )
group by email, name

edited Jul 05 '12 at 22:43

answered Jul 05 '12 at 20:48

Randy

16,480
1
37
55

1

You must mean MAX() from the OPs example. – MatBailie Jul 05 '12 at 21:14

score 0 · Answer 2 · answered Jul 05 '12 at 20:49

0

Is this what you want to achieve?

SELECT Email, Name, MAX(Id) FROM Table WHERE Id IN (3, 4) GROUP BY Email;

answered Jul 05 '12 at 20:49

Jorden van Foreest

645
5
18

1

Unless it's MySQL you *must* GROUP BY Name as well. – MatBailie Jul 05 '12 at 21:15
T-SQL has to have GROUP BY Name too. This also doesn't account for the same name having different email addresses, which is a possibility, given the table structure. – user1166147 Jul 05 '12 at 21:28

score 0 · Answer 3 · edited Jul 05 '12 at 21:24

0

Sometimes using Having Count(*) > 1 may be useful to find duplicated records.

select * from table group by Email having count(*) > 1

or

select * from table group by Email having count(*) > 1 and id > 3.

The solution provided before with the select MAX(ID) from table sounds good for this case. This maybe an alternative solution.

edited Jul 05 '12 at 21:24

Ben

51,770
36
127
149

answered Jul 05 '12 at 20:55

Israelm

1,607
3
23
28

user1166147 · Answer 4 · 2012-07-05T21:31:28.303

What RDMS are you using? This will return only one "Jimmy", using RANK():

SELECT A.email, A.name,A.id
FROM SO_Table A
INNER JOIN(
SELECT 
email, name,id,RANK() OVER (Partition BY name ORDER BY ID DESC) AS COUNTER
FROM SO_Table B  
) X ON X.ID = A.ID AND X.NAME = A.NAME
WHERE X.COUNTER = 1

Returns:

 email          name    id
 ------------------------------
 jimmy@domain.com   jimmy   4
 brian@domain.com   brian   4
 kevin@domain.com   kevin   3

SQL - Removing Duplicate without 'hard' coding?

4 Answers4