SQL Server Performance Forum – Threads Archive

show all columns on result

I need some help folks. I would like to know on how I would construct the sql query using group by. For example, table _temp_table contains 10 fields. You want to group by ColA and ColB but still wanted to show all fields select colA, colB from _temp_table group by ColA, ColB –this will work however, if we use * (wildcard), it will start to fail. select * from _temp_table group by ColA, ColB –this will not work so how do I write the query which will allow me to show all the columns but still grouping ColA and ColB? Any help would be greatly appreciated.

---

you can only use aggregate functions for the columns you are not grouping by. for instance: select a, b, min(c)
from blah
group by a, b SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
www.elsasoft.org

---

This is what I tried last night and looks like it worked. select * from _temp_table A
where exists (select colA, colB from _temp_table
where A.colA=colA and A.colB=colB group by ColA, ColB
)

---

won’t that just return every row in _temp_table? or am I missing something? SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
www.elsasoft.org

---

Actually, I forgot to include the HAVING clause. I should have included rows that are duplicates(or more) on those 2 fields but have different values on other fields. Final query is: SELECT * FROM _temp_table A
WHERE exists (select colA, colB FROM _temp_table
WHERE A.colA=colA and A.colB=colB
GROUP BY ColA, ColB
HAVING count(*) > 1
) This query will show rows that have more than 1 combination. But it will also show the other fields I needed.

---

]]>