I have the following SQL view:

SELECT		dbo.Course.CourseId,
			dbo.Course.Date,
			dbo.UserDetail.Name AS 'CustomerName',
			dbo.Product.Name AS 'ProductName',
			COUNT(dbo.CourseDelegate.UserId) AS 'DelegateCount'
FROM        dbo.Course
INNER JOIN	dbo.Product ON dbo.Course.ProductCode = dbo.Product.ProductCode
LEFT JOIN	dbo.UserDetail ON dbo.Course.CustomerId = dbo.UserDetail.UserId
LEFT JOIN	dbo.CourseDelegate ON dbo.Course.Courseid = dbo.CourseDelegate.CourseId
GROUP BY Course.CourseId, Course.[Date], UserDetail.Name, Product.Name

And the following code is generated by ES, to page and sort the data:

WITH [withStatement] AS
(
	SELECT DISTINCT c.[CourseId],c.[Date],c.[CustomerName],c.[ProductName],c.[DelegateCount],
	ROW_NUMBER() OVER( ORDER BY CustomerName ASC) AS ESRN
	FROM [vwCourseSummary] c
)
SELECT * FROM [withStatement] WHERE ESRN BETWEEN 1 AND 15

To cut a long(ish) story short, it's not working. The resulting data is always sorted by the first column (CourseId). It appears that the DelegateCount column (the COUNT() in the view) is the source of the problem. If I remove the DelegateCount column from the second SQL statement (i.e. the ES generated statement), everything works fine. As soon as the DelegateCount column is re-introduced, sorting breaks.

Does anyone have any idea why this is happening? Why does the introduction of an aggregate function in the view cause the sorting to break? Is this a bug with SQL Server? How can I fix / work around this problem?

Thanks

According to this article

http://blogs.msdn.com/queryoptteam/archive/2006/05/02/588731.aspx

the problem may be the SQL EntitySpaces generates. The inner ORDER BY only guarantees that ESRN will be calculated correctly. It does not guarantee that the outer SELECT will be returned in that order. Try this in Management Studio, and let me know if it fixes your issue:

WITH [withStatement] AS(	
    SELECT DISTINCT c.[CourseId],
        c.[Date],
        c.[CustomerName],
        c.[ProductName],
        c.[DelegateCount],	
        ROW_NUMBER() OVER( ORDER BY CustomerName ASC) AS ESRN	
    FROM [vwCourseSummary] c)
SELECT * FROM [withStatement] WHERE ESRN BETWEEN 1 AND 15
ORDER BY ESRN ASC

In the meantime, does dbo.UserDetail.Name have a non-unique, non-clustered index? If not, adding one may help the optimizer come up with the execution plan you want, even without the outer ORDER BY.

Ordering by ESRN does indeed work - I'd already given that a try in the Management Studio. I'll have a look at the indexes. I really need to get this working. This is in use in a DataGrid. It's the column sorting that's not working.

Can you fix this in ES (i.e. by simply adding the "ORDER BY ESRN" to its generated statements)?

Thanks David.