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 />

<br />
private bool uploadData(string myPath, string txtUpload)<br />
		{<br />
			bool bNoError = true;<br />
			string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +<br />
				"Data Source=" +myPath +txtUpload +";";<br />
<br />
			OleDbConnection con = new OleDbConnection(strConnection);<br />
			con.Open();<br />
			<br />
			<br />
			try<br />
			{<br />
				DataSet dsEWEtmp = new DataSet();<br />
				<br />
				OleDbDataAdapter daEWEForm = new OleDbDataAdapter("SELECT * FROM Some_Form", con);<br />
<br />
				<br />
				int updates = daEWEForm.Fill(dsEWEtmp, "Some_Form");<br />
				foreach (DataRow dataRow in dsEWEtmp.Tables)<br />
				{<br />
					dsEWEtmp.Tables[0].ImportRow(dataRow);<br />
					dsEWEtmp.Tables[0].AcceptChanges();<br />
				}<br />
<br />
				updates = daEWEForm.Update(dsEWEtmp);<br />
//				return dsEWEtmp;<br />
<br />
				DataTable tblEWEForm;<br />
				tblEWEForm = dsEWEtmp.Tables[0];<br />
			<br />
				OleDbCommandBuilder objCommandBuilder = new OleDbCommandBuilder(daEWEForm);<br />
				daEWEForm.Update(dsEWEtmp, "Some_Form");<br />
			}<br />
catch(Exception err)<br />
			{<br />
				string str = err.Message;<br />
				bNoError = false;<br />
					<br />
			}<br />
			finally<br />
			{<br />
				if (con.State.ToString() =="Open")<br />
				{<br />
					con.Close();<br />
				}<br />
			}<br />
			return bNoError;