Problem grouping by columns - 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

Problem grouping by columns

Author

Topic

rodrigovb
Starting Member

3 Posts

Posted - 2007-07-30 : 14:30:01

Hello guys !!

I'm actually a Mysql user, not a SQL Server user, just becouse the business I work uses it. But the problem I've had I think doesn't have any relation on the plataform it's running...

This is a construction software... I'll try my best to explain you : My table has 4 main columns (Face,Station,Combination,sAs). Face , Station and Combination form a primary key (never repeat together) and the "sAs" represents a calculus between some parameters.

The problem (it became a challenge already :D) consists in selecting the Face,Station and Combination where sAs is maximus, BUT grouping ONLY Face and Station.

For example :

Face Station Combination sAs
F1 0 Comb1 45
F1 0 Comb2 13
F1 0 Comb3 30
---
F1 10 Comb1 42
F1 10 Comb2 60
F1 10 Comb3 12
---
F2 0 Comb1 32
F2 0 Comb2 15
F2 0 Comb3 01
---
F2 10 Comb1 02
F2 10 Comb2 07
F2 10 Comb3 23

Here is the challenge :
If you execute the following query : "select Face,Station,Combination,max(sas) as sAS from test group by Face,Station" it returns you an arbitrary Combination for the rows in the resultset.

Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb1 60
F2 0 Comb1 32
F2 10 Comb1 23

But the combination I'd like to have is the combination related to the maximus sAs in F1/10, Comb2 instead of Comb1 returned..... The same occurs with the last row F2/10.

The query I wanna find should return the following resultset.

Face Station Combination sAs
F1 0 Comb1 45
F1 10 Comb2 60
F2 0 Comb1 32
F2 10 Comb3 23

Please, if somebody has any idea, share it to help me....

Thanks a lot..

Rodrigo

Some code to make it easier to try.... Maybe it has some difference between SQL Server and MySQL.

////////////// CUT HERE

create table test(
Face varchar(20),
Station int,
Combination varchar(20),
sAs int
);

insert into test values('F1', 0, 'Comb1', 45);
insert into test values('F1', 0, 'Comb2', 3);
insert into test values('F1', 0, 'Comb3', 30);
insert into test values('F1', 10, 'Comb1', 42);
insert into test values('F1', 10, 'Comb2', 60);
insert into test values('F1', 10, 'Comb3', 12);
insert into test values('F2', 0, 'Comb1', 32);
insert into test values('F2', 0, 'Comb2', 15);
insert into test values('F2', 0, 'Comb3', 01);
insert into test values('F2', 10, 'Comb1', 02);
insert into test values('F2', 10, 'Comb2', 07);
insert into test values('F2', 10, 'Comb3', 23);

select Face,Station,Combination,max(sas) as sAS from test group by Face,Station;
////////////// CUT HERE

Rodrigo Bornholdt

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-30 : 14:47:58

[code]
Declare @test table (Face varchar(20), Station int, Combination varchar(20), sAs int );

insert into @test values('F1', 0, 'Comb1', 45);
insert into @test values('F1', 0, 'Comb2', 3);
insert into @test values('F1', 0, 'Comb3', 30);
insert into @test values('F1', 10, 'Comb1', 42);
insert into @test values('F1', 10, 'Comb2', 60);
insert into @test values('F1', 10, 'Comb3', 12);
insert into @test values('F2', 0, 'Comb1', 32);
insert into @test values('F2', 0, 'Comb2', 15);
insert into @test values('F2', 0, 'Comb3', 01);
insert into @test values('F2', 10, 'Comb1', 02);
insert into @test values('F2', 10, 'Comb2', 07);
insert into @test values('F2', 10, 'Comb3', 23);

Select T2.Face , T2.Station , T2.Combination , T3. MaxSaS
from @test T2
Join (
select T.Face, T.Station , Max(T.sAs) as MaxSaS
from @test T
group by T.Face, T.Station
) T3 on T2.Face = T3.Face AND T2.Station = T3.Station And T2.sAs = T3.MaxSaS
Order by T2.Face , T2.Station , T2.Combination , T3. MaxSaS
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

rodrigovb
Starting Member

3 Posts

Posted - 2007-07-30 : 14:52:59

Hey guys !!

I've solved the challenge. I know it's just 10 minutes after I've posted the question, but I was think about this problem for a quite while...

I was taking a look in some other forums and trying some different solutions and finally found the query I wanted..

I'm listing the query for the people who are intersted in the solution...

SELECT q1.Face,q1.Station,q2.Combination,q1.sAs FROM
(select Face,Station,max(sas) as sAS from test group by Face,Station) as q1
LEFT JOIN
(SELECT Face,Station,Combination,sAs from test) as q2
ON q1.Face = q2.Face and q1.Station = q2.Station and q1.sAs = q2.sAs

If someone has any better idea, it's tottaly welcome...

Thanks

Rodrigo Bornholdt

rodrigovb
Starting Member

3 Posts

Posted - 2007-07-30 : 14:55:32

Perfect Dinakar Nethi !

I've spent some time desnecessary !! :D You've just posted the solution I'd found !! :D

Thanks anyway !!

Rodrigo

Rodrigo Bornholdt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources