toptensoftware/PetaPoco — pull request #94 by tawman (2011-12-28 06:56:10+00:00)
Title: Change rxOrderBy Regex and logic to be less greedy during Page<T> query rewrite to support sub-select ordering

Currently, PetaPoco does not support Page<T> operations on SQL statements that include an ORDER BY clause as part of a sub-select. The query rewrite logic only stores the first ORDER BY found for the COUNT, but removes all ORDER BY clauses for the items query causing a SQL error. I updated the Regex to perform a negative look ahead for a ') AS' after the ORDER BY, but other Regex forward/backward conditions could work as well. I also changed the Regex to search Right to Left whereby finding the last ORDER BY clause in the SQL statement first.

For example, the following method wants to perform a ROW_NUMBER() PARTITION BY query on the report date in order to return the most recent report generated on each day:

```c#
public Page GetReportsForMember(int pageNumber, int pageSize, string userName, Guid complianceGroupId, DateTime startDate, DateTime endDate, string orderBy)
{
var query = Sql.Builder
.Append("SELECT cr.*")
.Append("FROM (SELECT ROW_NUMBER() OVER (PARTITION BY CAST(cr.ReportDate AS DATE) ORDER BY ReportDate Desc) as samp_rn, cr.*")
.Append("FROM ComplianceReport cr")
.Append("INNER JOIN ScheduledReport sr ON cr.ComplianceGroupId = sr.ComplianceGroupId")
.Append("WHERE (sr.UserName = @0)", userName)
.Append("AND (sr.ComplianceGroupId = @0)", complianceGroupId)
.Append("AND (cr.Published = 1)");

if (startDate != DateTime.MinValue)
query.Append("AND (cr.ReportDate >= @0 AND cr.ReportDate <= @1)", startDate, endDate);

query.Append(") cr WHERE samp_rn = 1");

if (!string.IsNullOrEmpty(orderBy))
query.Append("ORDER BY " + orderBy);

return CmsDatabase.Page(pageNumber, pageSize, query);
}
```