MssqlSqlHelper::getTopSql

public function getTopSql($sql, $limit, $offset = 0)
{
	$offset = intval($offset);
	$limit = intval($limit);

	if ($offset > 0 && $limit <= 0)
		throw new MainArgumentException("Limit must be set if offset is set");

	if ($limit > 0)
	{
		if ($offset <= 0)
		{
			$sql = preg_replace("/^\s*SELECT/i", "SELECT TOP ".$limit, $sql);
		}
		else
		{
			$orderBy = '';
			$sqlTmp = $sql;

			preg_match_all("#\sorder\s+by\s#i", $sql, $matches, PREG_OFFSET_CAPTURE);
			if (isset($matches[0]) && is_array($matches[0]) && !empty($matches[0]))
			{
				$idx = $matches[0][count($matches[0]) - 1][1];
				$s = mb_substr($sql, $idx);
				if (substr_count($s, '(') === substr_count($s, ')'))
				{
					$orderBy = $s;
					$sqlTmp = mb_substr($sql, 0, $idx);
				}
			}

			if ($orderBy === '')
			{
				$orderBy = "ORDER BY (SELECT 1)";
				$sqlTmp = $sql;
			}

			// ROW_NUMBER() Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
			$sqlTmp = preg_replace(
				"/^\s*SELECT/i",
				"SELECT ROW_NUMBER() OVER (".$orderBy.") AS ROW_NUMBER_ALIAS,",
				$sqlTmp
			);

			$sql =
				"WITH ROW_NUMBER_QUERY_ALIAS AS (".$sqlTmp.") ".
				"SELECT * ".
				"FROM ROW_NUMBER_QUERY_ALIAS ".
				"WHERE ROW_NUMBER_ALIAS BETWEEN ".($offset + 1)." AND ".($offset + $limit);
		}
	}
	return $sql;
}