|
Subject:
|
Subquery. Any help would be appreciated.
|
|
Posted By:
|
rriis
|
Post Date:
|
9/25/2005 7:58:44 PM
|
Dataset below
Fields Num Name
1 A
1 A
1 A
1 B
2 C
3 D
3 D
I think this would be a subquery, but I only want to return records where the number has more than one name associated with it. Like in the dataset above, I would not care about Num2 and Num3, but would want to return Num1. The trick is I want to filter out the duplicates... so in this case the final return I would be looking for is:
Fields Num Name
1 A
1 B
Any takers?
rick
|
|
Reply By:
|
SqlMenace
|
Reply Date:
|
9/26/2005 9:17:10 PM
|
select num,name
from table
group by num,name
having count(*) > 1
this should do it I think (it's getting late)
http://sqlservercode.blogspot.com/
|
|
Reply By:
|
gemsuis
|
Reply Date:
|
9/29/2005 8:58:05 AM
|
select num,name
from table_name
group by num,name
having num = 1;
try this
|
|
Reply By:
|
Jeff Mason
|
Reply Date:
|
10/3/2005 12:04:26 PM
|
You are correct that you need a subquery, if I understand your requirement correctly. I don't think that either of the prior responses correctly give what you are looking for.
This is going to get a bit ugly, so let's take it in steps.
First off, this would be a lot easier if we had a table where the duplicates were removed. So, lets just group your data to remove them:
SELECT Num, Name
FROM yourtable
GROUP BY Num, Name
will give the following output:
1 A
1 B
2 C
3 D
Now, let's pretend we have a table with this data in it. I'm going to call this table 'grouptable'.
We want to group this table by Num, and find those groups with more than one row in them:
SELECT Num
FROM grouptable
GROUP BY Num
HAVING count(*)>1
will return:
1
Now we need to select the rows in 'grouptable' where the Num value is in this last resultset, so we combine these two queries:
SELECT Num, Name
FROM grouptable
WHERE Num IN
(SELECT Num
FROM grouptable
GROUP BY Num
HAVING count(*)>1)
Of course, 'grouptable' doesn't actually exist in the database. But, we can materialize such a table at will. In fact that's what a SELECT statement does. This is called a 'derived table'. We can use a derived table anywhere we use a "real" one; the only requirement is that we must give it a name/alias.
So, now we just just substitute the definition of 'grouptable' in both places:
SELECT Num, Name
FROM
(SELECT Num, Name
FROM yourtable
GROUP BY Num, Name) as gt1
WHERE Num IN
(SELECT Num
FROM
(SELECT Num, Name
FROM yourtable
GROUP BY Num, Name) as gt2
GROUP BY Num
HAVING count(*)>1)
yuck.
BTW, what is the primary key in the original table? You do have one, don't you? You're not going to get very far trying to maintain that table with those rows without one...
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
