group by statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

group by statement

Author

Topic

Ethan
Starting Member

6 Posts

Posted - 2007-02-06 : 04:54:52

Hi all, this is probably basic for most of you but I'm changing from mysql to sql server... and i've been searching for 2 hours now to this problem with no luck.

any help is most appreciated.

all i want to do is select unique names from a table along with any associated primary key.

select distinct(full_name) as full_name, record_id
from users
group by full_name

and I'm getting the dreaded
"invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
error.

I know that any unaggregrated column in the select list is causing this but can't seem to find a workaround.

Can someone help me get a distinct list of names, as well as any associated primary key?

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:58:18

select distinct full_name, record_id
from users

but the query has no meaning. If recordid is primary key, all values for any duplicate full_name already is distinct.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 04:59:11

Maybe this is what you want.
select full_name, min(record_id), max(record_id)
from users
group by full_name

Peter Larsson
Helsingborg, Sweden

Ethan
Starting Member

6 Posts

Posted - 2007-02-06 : 05:04:31

Wow this forum is amazing. I can't believe you guys replied so fast.

Anyway, yes it is somewhat meaningless but unfortunately mysql lets you do it.

And what is the alternative? Select the distinct names, and then for each row perform another query to get a record id? The number of queries could add up fast.

I will try your suggestions and let you know.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-06 : 05:07:21

select full_name, min(record_id), max(record_id), count(*)
from users
group by full_name

???
It may be better if you post some sample data, and your expected output based on the sample data you provide.

Peter Larsson
Helsingborg, Sweden

Ethan
Starting Member

6 Posts

Posted - 2007-02-06 : 05:08:25

Peso, that did the trick.

So simple yet so genius. I could actually even use the min and max of the record id's but I didn't mention it to keep the post simple.

Thanks again!!!

Ethan
Starting Member

6 Posts

Posted - 2007-02-06 : 05:14:32

quote:
Originally posted by Peso

select distinct full_name, record_id
from users

but the query has no meaning. If recordid is primary key, all values for any duplicate full_name already is distinct.

Peso, not sure I follow you 100% (it's late and my eyes are burning) but I have many records for "john smith", etc... that are common names.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources