SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem grouping by columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rodrigovb
Starting Member

Brazil
3 Posts

Posted - 07/30/2007 :  14:30:01  Show Profile  Click to see rodrigovb's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 07/30/2007 :  14:47:58  Show Profile  Visit dinakar's Homepage  Reply with Quote

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



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

rodrigovb
Starting Member

Brazil
3 Posts

Posted - 07/30/2007 :  14:52:59  Show Profile  Click to see rodrigovb's MSN Messenger address  Reply with Quote
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
Go to Top of Page

rodrigovb
Starting Member

Brazil
3 Posts

Posted - 07/30/2007 :  14:55:32  Show Profile  Click to see rodrigovb's MSN Messenger address  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000