using group by does not get the result

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

General SQL Server Forums

New to SQL Server Programming

using group by does not get the result

Author

Topic

sontong77
Starting Member

3 Posts

Posted - 2009-10-15 : 14:13:02

Please help...I have trouble to get the result using the gruop by phase. I appriciate your help.

People table has 5 columns (ID,Name,Cur_num,Person_id,address) is on sql server 2005 and has data as follow:

ID Name Cur_num Person_id address
1 john 1
2 john 2
3 sam 3
4 sam 5
5 sam 9
6 mike 12
7 mike 7
The following query returns row 2,5, and 6 with 2 fields: Name and Cur_num .

Select name, max(cur_num) From people Group by name

How do I modify the query to get the same rows with all fields?

I also have the follow sql and sql server complaint its syntax. i don't know why.

Select p.*, s.*
From people p inner join (Select name, max(cur_num) From people Group by name) s
On p.name=s.name and p.cur_num=s.cur_num

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 14:19:00

Here's one way that should work with whatever sql server version you have. However if a person has multiple rows with the same (max) cur_num then all those rows would be returned. If that is a problem post back:


select p.ID
       ,p.Name
       ,p.Cur_num
       ,p.Person_id
       ,p.address
from   people p
inner join 
       (
       Select name
              ,max(cur_num) as cur_num
       From   people 
       Group by name
       ) d
              on d.name = p.name
              and d.cur_num = p.cur_num

Be One with the Optimizer
TG

sontong77
Starting Member

3 Posts

Posted - 2009-10-15 : 14:32:02

Hi TG,

I just try your sample code on sql server 2005 and IT WORKS PERFECTLY!

Thank you so much for your help. I appriciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-15 : 14:36:01

You're welcome.
Since you have 2005 the code here may be safer if the same person_id will ever have multiple rows with the same max(cur_num):
http://ask.sqlteam.com/questions/256/i-have-trouble-getting-the-right-result-using-the-group-by-phase

Be One with the Optimizer
TG

sontong77
Starting Member

3 Posts

Posted - 2009-10-16 : 11:13:54

Hi TG,

if I move the query into store procedure, will it run faster?

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-16 : 11:22:39

Not necessarily but it couldn't hurt either. The performance boost would mostly come when sql server has an execution plan already cached for that statement or SP. There are advantages to using SPs though especially if the code is being called from an application. For instance: security can be tighter because if you GRANT EXEC on the SP to a user or role then you don't need to GRANT SELECT on the specific tables.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources