Monthly Archives: October 2011

SQL

Select records in specific order in SQL


In order to obtain result records in spec. order you can apply next query:

string strRowIDs = "NULL";
string strSelectOrder = string.Empty;
if (aRowIDs.Count > 0)
{
	int nCnt = 0;
	strRowIDs = string.Empty;
	strSelectOrder = "select_order = case _id";
	foreach (int i in aRowIDs)
	{
		strSelectOrder += " when " + i.ToString() + " then " + (nCnt++).ToString();
		strRowIDs += "," + i.ToString();
	}
	strSelectOrder += " end";
}
string strWhere = "_id in (" + strRowIDs.Trim(new char[] { ',' }) + ")";

// Get data
/*
select *,strSelectOrder
from XXX
where strWhere 
order by select_order
*/

where aRowIDs- set of IDs
select_order – new order