CODEWITHSUNDEEP
sqlsql-servert-sql
Jack Clarke
Jack Clarke

Reputation: 55

Select a single record from a priority based list of values

Apologies if the title is confusing, i'm finding it hard to put into words exactly what i'm looking for. If someone can point me in the correct direction or if another question exists, letting me know would be much appreciated.

If I have the following values in a table I wish to write a query that selects a single record for each [ContactID].

The query should check first if a record exists with [PhoneType] 'Mob' then --> 'Work' then --> 'Hom' and return the first record that matches and ignore the other records for the [ContactID]

CREATE TABLE #Test
(
    [ContactID] int,
    [PhoneNo] NVARCHAR(20),
    [PhoneType] NVARCHAR(20)
);

INSERT INTO #Test
(
    [ContactID],
    [PhoneNo],
    [PhoneType]
)
VALUES
    (1, '51351301', 'Mob'),
    (1, '999', 'Work'),
    (1, '8461', 'Hom'),
    (2, '213231321', 'Work'),
    (3, '848136', 'Mob'),
    (4, '23231', 'Hom'),
    (4, '2132315', 'Work')

The result would look like 

(1, '51351301', 'Mob'),
(2, '213231321', 'Work'),
(3, '848136', 'Mob'),
(4, '2132315', 'Work')

Upvotes: 1

Views: 1335

Answers (3)

Brad
Brad

Reputation: 3591

I think this will do what you want. It sets a row ID for each ContactID, and only pulls the first one for each ContactID

CREATE TABLE #Test
(
    [ContactID] int,
    [PhoneNo] NVARCHAR(20),
    [PhoneType] NVARCHAR(20)
);

--DROP TABLE #Test

INSERT INTO #Test
(
    [ContactID],
    [PhoneNo],
    [PhoneType]
)
VALUES
    (1, '51351301', 'Mob'),
    (1, '999', 'Work'),
    (1, '8461', 'Hom'),
    (2, '213231321', 'Work'),
    (3, '848136', 'Mob'),
    (4, '23231', 'Hom'),
    (5, '2132315', 'Work')


SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY PhoneType ASC) AS RowNum
    FROM #Test
    WHERE PhoneType IN ('Mob', 'Work', 'Hom')
) Sub 
WHERE RowNum = 1

If you want to get specific phone type first you can use this select statement for ronum

SELECT *, ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY  CASE WHEN PhoneType = 'Mob' THEN 1 WHEN PhoneType = 'Work' THEN 2 WHEN PhoneType = 'Hom' THEN 3 END ASC) AS RowNum
FROM #Test
WHERE PhoneType IN ('Mob', 'Work', 'Hom')

Upvotes: 2

DineshDB
DineshDB

Reputation: 6193

Use window function with top(1) with ties

Try this:

SELECT * 
FROM(
    SELECT TOP(1) WITH ties *
    FROM #Test t
    ORDER BY ROW_NUMBER() OVER (PARTITION BY contactid
                                ORDER BY (CASE WHEN phoneType = 'Mob' THEN 1
                                            WHEN phoneType = 'Work' THEN 2
                                            WHEN phoneType = 'Hom' THEN 3
                                            ELSE 4
                                            END)
                                   )
    )D
ORDER BY D.ContactID

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by contactid
                                order by (case when phoneType = 'Mob' then 1
                                               when phoneType = 'Work' then 2
                                               when phoneType = 'Hom' then 3
                                               else 4
                                          end)
                               ) as seqnum
      from #test t
     ) t
where seqnum = 1;

If you have a separate table with contacts, you can also use apply:

select t.*
from contacts c cross apply
     (select top (1) t.*
      from #test t
      where c.contactid = t.contactid
      order by charindex(phonetype, 'Mob,Work,Hom')
     ) t;

Note that this uses a shorthand for the ordering. This will work for your phone types -- but assumes these are the only three.

Upvotes: 2

Related Questions