Database

asha_s wrote:

i have many room that have the same featuer and i want to chose the room that are its size greater than the sumCap

select sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum desc

asha_s wrote:

( i have many room that are grater than the sumCap and i want to chose the one that have the minman capacity from them )

select top 1 sum(capacity) as CapacitySum,feature,<br />
(select top 1 t1.roomid from myTable as t1 where t1.feature = myTable.feature order by t1.roomid desc) as roomID<br />
from myTable <br />
group by feature<br />
order by CapacitySum asc

select top 1  min(roomID) as RoomID,feature from myTable where roomid >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by roomID asc<br />

select top 1 min(Capacity ) as Capacity,feature from myTable where Capacity >=sumCap<br />
group by roomid,feature<br />
having (feature='1100')<br />
order by Capacity asc<br />

abdurrahman o wrote:

is there any suggestions?

SELECT <br />
	SNO,<br />
	Id,<br />
(<br />
	SELECT <br />
		(COUNT(*) + 1) <br />
	FROM <br />
		table1 b <br />
	WHERE <br />
		b.SNO < a.SNO AND <br />
		a.Id = b.Id)<br />
FROM <br />
	table1 a<br />

<br />
SELECT <br />
	SNO,<br />
	Id,<br />
(<br />
	SELECT <br />
		COUNT(*)<br />
	FROM <br />
		table1 b <br />
	WHERE <br />
		b.SNO <= a.SNO AND <br />
		a.Id = b.Id)<br />
FROM <br />
	table1 a<br />