March 25, 2004 at 10:17 pm
Earlier today I was working with a query that selected several fields and then used a group by to eliminate duplicates. Something like this
Select cust_id, unit, name, phone_no, addr_1, count(cust_id) as counter
From customers
Group by cust_id
However I made a change to my query and foolishly didn't backup the original. Now I get the error name is not valid in the select statement because it is not contained in either an aggregate function or the GROUP BY clause. I don't what I did to make it work and then not work, it really baffles me. My main goal here is to get a query that will select just a single cust_id in cases of duplicates. Such as
cust_id unit name phone_no addr_1
aaa000 1 smith 999-999-9999 123 something ln
aaa000 2 smith 999-999-9999 123 something ln
bbb000 1 blake 888-888-8888 231 somethin ln
ccc000 1 louis 777-777-7777 123 other ln
The group by I had before removed the second aaa000 from the results. I'm sure this is a simple answer but its been a annoyance for me.
March 25, 2004 at 11:52 pm
CHeck this
Use MasterSelect SysPro1.program_name,SysPro1.login_time,SysPro1.MemUsage,SysPro1.Hostname,SysPro1.Hostname,SysPro1.nt_domain,SysPro1.nt_usernameFrom sysprocesses SysPro1INNER JOIN(SELECT program_name, MAX(login_time) as T2FROM sysprocessesGROUP BY program_name Having len(program_name) > 0) SysPro2ON( SysPro2.program_name = SysPro1.program_name AND SysPro2.T2 = SysPro1.login_time )My Blog:
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply