February 26, 2004 at 7:32 am
Hi all, I am trying to write a query that has my brain in a knot. I have a table which contains employees. I wish to select only employees who have a surname that only appears once. So, for example, if I had one fellow named Baker and 3 named Smith, I want the query to simply return Baker and dnore the Smiths because their surname appeared more than once. any help appreciated. jw
February 26, 2004 at 8:13 am
If I am reading what you are writing correctly, you would like the results to only return Baker, because that name only appears 1 time. Here is the code that will do that. just copy it into query analyzer and it should work.
declare @t table (
tid int,
tname varchar(5)
)
INSERT INTO @T (tid, tname) VALUES (2,'Baker')
INSERT INTO @T (tid, tname) VALUES (3,'Smith')
INSERT INTO @T (tid, tname) VALUES (4,'Smith')
INSERT INTO @T (tid, tname) VALUES (5,'Smith')
select distinct tname from @t group by tname having count(tname) = 1
February 26, 2004 at 8:14 am
forgot to delete the distinct from the query. It is redundant.
February 26, 2004 at 8:35 am
Yup, that did it. Thanks David
March 4, 2004 at 11:40 am
Why not:
SELECT COUNT(*), Surname FROM Employees
GROUP BY Surname
HAVING COUNT(*) = 1
March 4, 2004 at 1:19 pm
Randy,
Your suggestion returns too much. It returns the count (SELECT COUNT(*)) which isn't what the poster requested.
-SQLBill
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply