CODEWITHSUNDEEP
sqlsql-server
mhn
mhn

Reputation: 2750

SELECT distinct rows grouped by column name

I have a scenario where i need to pick the latest Id of an employee . The id can be a varchar or int

id      mail
-------------
1234    [email protected]
5678    [email protected]
C123    [email protected]
C234    [email protected]

Now, my query result just has to be 

id      mail
-------------
5678    [email protected]
C234    [email protected]

The logic is, If there is an integer against the mail id, the highest integer has to be picked. If there is no integer, the varchar value can be picked.

Tried

select max(id), mail from worker  group by mail

But it picks the varchar value when there is a duplicate mail id.

Upvotes: 0

Views: 72

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

One solution I can think of is:

select MAX(t.id), t.email 
from ( 
   select * from Emails where ISNUMERIC(id) = 1 
      union
   select * from Emails where ISNUMERIC(id) <> 1 and email not in 
                              (select email from Emails where ISNUMERIC(id) = 1)
) as t
group by t.email

Upvotes: 0

Jason W
Jason W

Reputation: 13179

I would use the isnumeric value and ROW_NUMBER to select the right value.

DECLARE @T TABLE (id VARCHAR(10), mail VARCHAR(100))
INSERT @T VALUES 
    ('1234', '[email protected]'),
    ('5678', '[email protected]'),
    ('C123', '[email protected]'),
    ('C234', '[email protected]')

SELECT id, mail
FROM (
    SELECT
        id,
        mail,
        ROW_NUMBER() OVER (
            PARTITION BY
                mail
            ORDER BY
                CASE WHEN ISNUMERIC(id) = 1 THEN CONVERT(INT, id) ELSE 0 END DESC,
                id) AS RowNum
    FROM @T
    ) T
WHERE RowNum = 1

The output yields

id         mail
---------- -------------
C234       [email protected]
5678       [email protected]

Upvotes: 2

Related Questions