SQL grouping by all the columns
The DISTINCT Keyword
I believe what you are trying to do is:
SELECT DISTINCT * FROM MyFooTable;
If you group by all columns, you are just requesting that duplicate data be removed.
For example a table with the following data:
id | value
----+----------------
1 | foo
2 | bar
1 | foo
3 | something else
If you perform the following query which is essentially the same as SELECT * FROM MyFooTable GROUP BY *
if you are assuming * means all columns:
SELECT * FROM MyFooTable GROUP BY id, value;
id | value
----+----------------
1 | foo
3 | something else
2 | bar
It removes all duplicate values, which essentially makes it semantically identical to using the DISTINCT keyword with the exception of the ordering of results. For example:
SELECT DISTINCT * FROM MyFooTable;
id | value
----+----------------
1 | foo
2 | bar
3 | something else
He is trying find and display the duplicate rows in a table.
SELECT *, COUNT(*) AS NoOfOccurrences
FROM TableName GROUP BY *
HAVING COUNT(*) > 1
Do we have a simple way to accomplish this?
If you are using SqlServer the distinct keyword should work for you. (Not sure about other databases)
declare @t table (a int , b int)
insert into @t (a,b) select 1, 1
insert into @t (a,b) select 1, 2
insert into @t (a,b) select 1, 1
select distinct * from @t
results in
a b
1 1
1 2
I wanted to do counts and sums over full resultset. I achieved grouping by all with GROUP BY 1=1
.
nope. are you trying to do some aggregation? if so, you could do something like this to get what you need
;with a as
(
select sum(IntField) as Total
from Table
group by CharField
)
select *, a.Total
from Table t
inner join a
on t.Field=a.Field