Mr Lahey Mr Lahey - 5 months ago 9
SQL Question

MSSQL: Select 1 record of each result with same value (similiar to FirstOrDefault)

I have a list of records with duplicate phone numbers. We need to arbitrarily assign 1 record to be the master, so what I'm trying to do is write an MS SQL script to grab the first instance of each active Phone number and ignore the rest. Similar to how FirstOrDefault works in C# Linq.

Example Data

Steve | 555-1234 | Active
Mike | 555-1234 | Active
Bryan | 555-1234 | Inactive
Jane | 555-5678 | Active
Tara | 555-5678 | Inactive
Pete | 555-5678 | Active


So I want my results to show 1 entry of 555-1234 and 1 of 555-5678 but only of my active records. I don't care which of the active names I get as I'm arbitrarily assigning one as a master.

Answer

Based on this schema:

CREATE TABLE MyPeeps (
     Name VARCHAR(10),
     Phone VARCHAR (10),
     Status VARCHAR( 10)
);

INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Steve','555-1234','Active');
INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Mike','555-1234','Active');
INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Bryan','555-1234','Inactive');
INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Jane','555-5678','Active');
INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Tara','555-5678','Inactive');
INSERT INTO MyPeeps (Name, Phone, Status) VALUES ('Pete','555-5678','Active');

This works:

SELECT PHONE 
FROM MyPeeps 
WHERE Status = 'Active'
GROUP BY PHONE;

See here: http://sqlfiddle.com/#!9/086e79/2

Comments