SQL Server Performance Forum – Threads Archive

display all records based on 1 distinct column

Have the table structure like this AreaCode Region
21KTR
21STR-I
22STR-I
232STR-I
233STR-I
235STR-I
238STR-I
242STR-V
243STR-V
244STR-V
25STR-I Want to see all Columns which have more than 1 same area code
please Help me

---

SELECT Region
FROM < your_table >
GROUP BY Region
HAVING COUNT(*)>1
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

want to display all 2 columns

---

Given the above sample data, what should the resultset look like? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

AreaCode Region
21 KTR
21 STR-I
22 STR-I
232 STR-I
233 STR-I
235 STR-I
238 STR-I
242 STR-V
243 STR-V
244 STR-V
25 STR-I The result should b look like this
21 KTR
21 STR-I

---

You change the GROUP BY query to group by AreaCode, and use it as a subquery: SELECT A.AreCode, A.Region FROM AreaCodeRegion A
WHERE A.AreaCode
IN (SELECT B.AreaCode FROM AreaCodeRegion B GROUP BY B.AreaCode HAVING COUNT(*) > 1)

---

<pre id="code"><font face="courier" size="2" id="code"><br />SELECT * <br /> FROM &lt; your_table &gt;<br /> WHERE AreaCode IN<br /> (SELECT AreaCode<br /> FROM &lt; your_table &gt;<br /> GROUP BY AreaCode<br /> HAVING COUNT(*)&gt;1)<br /><br />–or<br /><br />SELECT t.* <br /> FROM &lt; your_table &gt;<br /> JOIN <br /> (SELECT AreaCode<br /> FROM &lt; your_table &gt;<br /> GROUP BY AreaCode<br /> HAVING COUNT(*)&gt;1) x<br /> ON t.AreaCode = x.AreaCode<br /></font id="code"></pre id="code"><br /><br />Sorry, missed the duplicate AreaCode 21 in my first posting. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

---

Try using this SELECT AreaCode,Region FROM <your table>
where AreaCode in(select AreaCode from <your table> group by AreaCode having count(*)>1) cheers
Pali
Everything happens for your own good.

---

Thanks Alot [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

]]>