CODEWITHSUNDEEP
sqlgroup-byaggregate-functionshaving
Baris Kilic
Baris Kilic

Reputation: 51

GROUP BY without specific column

I try to do this, but it won't let me.

My Table (MSSQL):

+----------+--------+---------------------+----------+-------+------------+
| parentid | okeyno |      okeyname       | okeydata | recid | okeydouble |
+----------+--------+---------------------+----------+-------+------------+
|     1357 |      1 | WINZERKE01_TESTFELD |      123 |  1486 |          0 |
|     1360 |      1 | WINZERKE01_TESTFELD |      456 |  1488 |          0 |
|     1362 |      1 | WINZERKE01_TESTFELD |      789 |  1492 |          0 |
|     1361 |      1 | WINZERKE01_TESTFELD |      789 |  1490 |          0 |
|     1358 |      1 | WINZERKE01_TESTFELD |      123 |  1484 |          0 |
|     1377 |      1 | WINZERKE01_TESTFELD |      999 |  1485 |          0 |
+----------+--------+---------------------+----------+-------+------------+

What you can see, is that there are several entrys with the same okeyname. I now want only the parentid of the row, where there is no dublicate okeydata.

in this case the result should be "1360" and "1377".

Now this is what I've trying to do:

SELECT parentid FROM objkeys WHERE okeyname = 'WINZERKE01_TESTFELD' GROUP BY okeydata HAVING COUNT(okeydata) = 1

But it always wants, that I include parentid into the GROUP BY. But I don't want this. How could I solve that problem?

Thanks.

Upvotes: 0

Views: 50

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Since you know that the result only represents a single row, there should be no issue with applying any of the aggregates which return their input if only given one input row. E.g. MIN, MAX, SUM:

SELECT 
    okeydata, MIN(parentid) as parentid
FROM 
    objkeys
WHERE 
    okeyname = 'WINZERKE01_TESTFELD'
GROUP BY 
    okeydata
HAVING 
    COUNT(okeydata) = 1

Upvotes: 2

Related Questions