CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Colt Stumpf
Colt Stumpf

Reputation: 121

MS SQL Server 2008 error on GROUP BY

I have a table I am trying to clean up so I just need the first address in the table for each ClientId

Table Addresses has these columns

Pk[Id]
[ClientId]
[AddressLine1]
[AddressLine2]

Query I use:

SELECT *
FROM Addresses
ORDER BY ClientId

result = 

1   1   foo     bar
2   1   foo2    bar2
3   1   foo3    bar3
4   1   foo4    bar4
5   2   foo     bar2
95  2   foo     bar5
97  2   foo     bar6
8   3   foo2    bar7

wanted result = 

1   1   foo     bar   <--is first match for clientid = 1
5   2   foo     bar2  <-- is first match for clientid = 2
8   3   foo2    bar7  <-- is first match for clientid = 3

This need to work for n clientids

I tried

SELECT *
FROM Addresses
GROUP BY ClientId

The resulting error is (Column 'Id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.)

What am I missing here?

Upvotes: 0

Views: 993

Answers (4)

Taryn
Taryn

Reputation: 247680

SQL Server requires that when using a GROUP BY you need to either use an aggregate function on the columns in the select list or add them to the GROUP BY.

Your original query could be changed to use the following:

select a.id, a.clientid, a.address1, a.address2
from addresses a
inner join
(
    select clientid, MIN(id) id
    from addresses
    group by clientid
) d
    on a.clientid = d.clientid
    and a.id = d.id;

As you can see this uses a subquery that returns the min(id) for each clientId, this only groups by the clientid. You then join this to your table to return only those rows with the min id for each client.

Upvotes: 2

Vivek Sadh
Vivek Sadh

Reputation: 4268

As per the Single-Value Rule: all columns in the select-list must be explicitly part of the GROUP BY criteria, or else inside an aggregate function, e.g. COUNT(), MAX(), etc.

Upvotes: 0

jods
jods

Reputation: 4591

You did SELECT *. Every field in the select list must either be in the GROUP BY clause or inside an agregation function (MAX, MIN, SUM, AVG, etc.).

This explains the error. For the SQL that solves your problem see Goat_CO answer.

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

You are getting multiple lines because you're selecting a field you don't want to be grouping by. If you just want the first entry for each clientID you can use an analytic function like ROW_NUMBER Try:

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY ID) as RowRank
      FROM Addresses)sub
WHERE RowRank = 1

Upvotes: 2

Related Questions