Binaryworld - Complex grouping problem. How to group records by minimum of field1 , ... [ SQL Server -> T-SQL ]

In this example we assume that we have 3 hotels and their codes are AIR, PID, ZLM. For each property we have multiple rates for each week and this rate should be picked up on the basis of sellorder -> seasonlength -> rate .
We have multiple seasonnumber for each week but only one season rate should be displayed. Rate should be picked up as folloing.
->select lowest sellorder if multiple seasons for a week
-> if sellorder has same value for all records for a week then select lowest seasonlength and seasonlength also same then select maximum rate

Click here to copy the following block

if object_id('t1') is not null
drop table t1
go
create table t1
(
i int identity(1,1)
,p char(3) --Propid
,w int --Week_Number
,sn int --seasonNumber
,so int --Sellorder
,sl int --season length
,rt money --Rate
)
go

/*
================================================
Note:
================================================
For each property multiple seasonnumber for each week
but only one season rate should be displayed
-> select lowest sellorder if multiple seasons for a week
-> if sellorder has same value for all records for a week then
select lowest seasonlength and seasonlength also same then
select maximum rate
*/

INSERT INTO t1 VALUES('AIR',1,5,20,111,10.11)
INSERT INTO t1 VALUES('AIR',1,6,21,111,20.34)
INSERT INTO t1 VALUES('AIR',2,7,23,111,30.22)
INSERT INTO t1 VALUES('AIR',2,8,23,111,40.12)
INSERT INTO t1 VALUES('PID',1,9,20,110,10.56)
INSERT INTO t1 VALUES('PID',1,10,20,111,10.57)
INSERT INTO t1 VALUES('PID',2,11,20,111,10.44)
INSERT INTO t1 VALUES('PID',2,12,20,111,10.34)
INSERT INTO t1 VALUES('ZLM',1,13,20,111,10.22)
INSERT INTO t1 VALUES('ZLM',1,14,20,111,20.45)
INSERT INTO t1 VALUES('ZLM',2,15,20,111,10.67)
INSERT INTO t1 VALUES('ZLM',2,16,20,111,20.89)

go

--select * from t1

select --//Get max rate
t.i ,t.p ,t.w ,t.sn ,t.so ,t.sl ,t.rt
from(
select --//Get min seasonlength
t.i ,t.p ,t.w ,t.sn ,t.so ,t.sl ,t.rt
from(
select --//Get min sellorder
t.i ,t.p ,t.w ,t.sn ,t.so ,t.sl ,t.rt
from t1 t
where so IN (select min(so) from t1 where p=t.p and w=t.w)
group by t.i ,t.p,t.w ,t.sn ,t.so ,t.sl ,t.rt
) a
join t1 t
on t.i=a.i
where t.sl IN (select min(sl) from t1 where p=t.p and w=t.w)
group by t.i ,t.p,t.w ,t.sn ,t.so ,t.sl ,t.rt
) a
join t1 t
on t.i=a.i
where t.rt IN (select max(rt) from t1 where p=t.p and w=t.w)
group by t.i ,t.p,t.w ,t.sn ,t.so ,t.sl ,t.rt