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 sAsF1 0 Comb1 45F1 0 Comb2 13F1 0 Comb3 30---F1 10 Comb1 42F1 10 Comb2 60F1 10 Comb3 12---F2 0 Comb1 32F2 0 Comb2 15F2 0 Comb3 01---F2 10 Comb1 02F2 10 Comb2 07F2 10 Comb3 23Here 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 sAsF1 0 Comb1 45F1 10 Comb1 60F2 0 Comb1 32F2 10 Comb1 23But 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 sAsF1 0 Comb1 45F1 10 Comb2 60F2 0 Comb1 32F2 10 Comb3 23Please, 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 HEREcreate 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 HERERodrigo 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. MaxSaSfrom @test T2Join ( 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.MaxSaSOrder 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.sAsIf someone has any better idea, it's tottaly welcome...ThanksRodrigo 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 !! :DThanks anyway !!RodrigoRodrigo Bornholdt |
|
|
|
|
|