Group question - 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

General SQL Server Forums

New to SQL Server Programming

Group question

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-26 : 16:05:03

Hi all. Here is my rookie question.

My table is like:
pos1 pos2 attr value
2 10 A 1.2
2 10 B 2.1
2 10 C 10
...
10 29 A 2
10 29 B 100
...

Now I want to get for each pair of pos1 and pos2, the max value regardless of attr. so it should be like
pos1 pos2 value
2 10 10
10 29 100
...

I tried group by pos1, but then I can not grab pos2.

Anyone could help me out of this?

Thanks.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 16:07:52

group by both then,

select 
	pos1,pos2,max(value)
from 
	yourtable
group by 
	pos1,pos2

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-26 : 16:10:22

I tried, but the pos1 and pos2 looks exchanged. So I am not sure whether it is correct.

quote:
Originally posted by sakets_2000

group by both then,

select
pos1,pos2,max(value)
from
yourtable
group by
pos1,pos2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 16:11:14

exchanged meaning ?

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-26 : 16:14:37

"Exchanged" means I expect to be like
pos1 pos2
2 10

but finally it showed up with
pos1 pos2
10 2

I am not quite sure whether it is correct because my table is very large.

quote:
Originally posted by sakets_2000

exchanged meaning ?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 16:18:12

No, That wouldn't happen. If result set is showing you a value (10,2), then that combination surely exists. Why don't you run a select and confirm.

select * from yourtable where pos1=10 and pos2=2

mavershang
Posting Yak Master

111 Posts

Posted - 2009-03-26 : 16:30:02

All right, I will check that. Thank you very much.

quote:
Originally posted by sakets_2000

No, That wouldn't happen. If result set is showing you a value (10,2), then that combination surely exists. Why don't you run a select and confirm.

select * from yourtable where pos1=10 and pos2=2

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 16:31:24

np.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources