CODEWITHSUNDEEP
sqlsql-server
Ayal
Ayal

Reputation: 133

Select with column that no in the group by SQL Server

I want to select a column that is not in the GROUP BY.

My code:

 SELECT 
     dbo.func(field1, field2), field3
 FROM
     table
 WHERE
     field4 = 1224
 GROUP BY
     dbo.func(field1, field2), field3
 HAVING
     COUNT(id) > 1

And I want to select also the column id like this:

 SELECT 
     id, dbo.func(field1, field2), field3
 FROM
     table
 WHERE
     field4 = 1224
 GROUP BY
     dbo.func(field1, field2), field3
 HAVING
     COUNT(id) > 1

Upvotes: 0

Views: 72

Answers (2)

Andomar
Andomar

Reputation: 238086

You could join back to the original table to retrieve the matching row(s) with id:

SELECT  t.id
,       filter.funresult
,       t.field3
FROM    table t
JOIN    (
        SELECT  dbo.func(field1,field2) as funresult
        ,       field3
        FROM    table
        WHERE   field4 = 1224
        GROUP BY
                dbo.func(field1,field2)
        ,       field3
        HAVING  COUNT(id) > 1
        ) filter
ON      filter.funresult = dbo.func(t.field1, t.field2)
        AND filter.field3 = t.field3

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I suspect that you want to apply a count restriction and then return all matching records from the original table, along with the output of the scalar function. One approach is to use COUNT as analytic function with a partition which corresponds to the columns which appeared in your original GROUP BY clause. The difference here is that we don't actually aggregate the original table.

WITH cte AS (
    SELECT id, dbo.func(field1, field2) AS out, field3,
        COUNT(id) OVER (PARTITION BY dbo.func(field1, field2), field3) cnt
    FROM yourTable
    WHERE field4 = 1224
)

SELECT id, out, field3
FROM cte
WHERE cnt > 1;

Upvotes: 5

Related Questions