Menu
I have this table:
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+
Is there a way to group by the key to get this result?
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 2 | bar |111000 |
| 3 | foo |000111 |
+----+-----+----------+
Or this result:
+----+-----+----------+
| id | name| key |
+----+-----+----------+
| 1 | foo |111000 |
| 3 | foo |000111 |
+----+-----+----------+
If I use this query:
SELECT * FROM sch.mytable GROUP BY(key);
This is not correct I know that, because I should group by all the columns that I need to show.
Is there a solution for this problem?
893
SELECT * FROM sch. mytable GROUP BY(key);
GROUP BY does not remove duplicates.
GROUP BY only treats two rows as duplicates if all the column values in both the rows are the same. If even a single column value in either of the row is non-matching, they are treated as unique.
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause. The original idea was to create the table in beginning of the query, so the (SELECT * FROM #TBL) could be used on the query itself, instead of defining the names on each GROUP BY.
A query that works for all DB engines would be
select t1.*
from sch.mytable t1
join
(
SELECT min(id) as id
FROM sch.mytable
GROUP BY key
) t2 on t1.id = t2.id
where min(id) is the function that influences which result you get. If you use max(id) you get the other.
192
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
