Feature ordering lost for database layers using WMS

[geographika]

I have several scenarios where I need to be able to order features, so more recent ones are displayed at the top. I access these layers using WMS. However as soon as the bounding-box WHERE clause is wrapped around the SQL defined in the layer's DATA clause and ORDER BY statement is ignored (silently).

Original DATA clause:

    DATA "GEOM from (
        SELECT TOP 10000000 -- required for ordering
        ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl USING UNIQUE WorksId USING SRID=3857"

Query passed to database for WMS request - records are returned in a random order, as the ORDER BY is in a subquery, and not the outer SELECT:

SELECT ... from (
        SELECT TOP 10000000 -- required for ordering
        ID, GEOM, Name
        FROM t
        ORDER BY Name
        ) as tbl WHERE GEOM.STIntersects(geometry::STGeomFromText('POLYGON((-1639825.98675614 6497843.86227236,-122771.013243864 6497843.86227236,-122771.013243864 7349352.13772764,-1639825.98675614 7349352.13772764,-1639825.98675614 6497843.86227236))',3857)) = 1 

When you use an ORDER BY clause in a view, an inline function, a derived table, or a subquery, it does not guarantee ordered output. Instead, the ORDER BY clause is only used to guarantee that the result set that is generated by the Top operator has a consistent makeup. The ORDER BY clause only guarantees an ordered result set when it is specified in the outermost SELECT statement.

http://support2.microsoft.com/kb/841845

I am using MS SQL Server, however I believe this behaviour may be applicable to all databases, and there are many MapServer examples which make use of ORDER BY to define layer drawing order e.g.
http://www.mapgears.com/en/blog/archive/2013-03-05-roads_network_mapping

To resolve this a LAYER parameter (named perhaps ORDER) could be used to set a field name to add an ORDER BY clause to the outer SELECT.
Is this something that would have to be added to all drivers, or could it be added in one place?

I posted this issue to the mailing list and thought I had a solution, but the SELECT TOP does not in fact keep ordering.

http://lists.osgeo.org/pipermail/mapserver-users/2014-April/076355.html

[geographika]

My temporary workaround is to hard code an ORDER BY field (ROWNUM) into the SQL select statements in the driver I am using (MSSQL):

geographika@2122449

In the DATA clause for each layer that requires ordering I add the following to the SELECT statement:

SELECT ROW_NUMBER() OVER(ORDER BY [TimeStamp] ASC, ID ASC) AS ROWNUM

In this example I am sorting by two fields. This allows complete flexibility on how features are ordered,
even with the hard coded name.

For these layers I set the plugin to the modified DLL:

CONNECTIONTYPE PLUGIN 
PLUGIN "ms/plugins/mssql2008/msplugin_mssql2008_sorted.dll"

[tbonfort]

I'm not sure I like the SORTFIELD keyword: as you said you'd also need a keyword for ASC/DESC, and what happens if you need to sort on multiple columns. I would think that an "order by" keyword in the DATA statement is more flexible as its content would be directly injected in the SQL sent to the database without the need for mapserver to have to interpret it.

[geographika]

@tbonfort - yes that would be simpler and more flexible. I only considered a keyword as this seems to be implmented already for WFS layers (via a URL rather than in the MAP file).

[szekerest]

@geographika - Are you willing to implement for the MSSQL driver what @tbonfort suggested or need assistance in this matter?

[geographika]

@szekerest - I've sent an email to you on this. My C coding is very rusty, so would appreciate some input.

[szekerest]

Implemented for mssql in master in e653b9b

[geographika]

@szekerest I just tested this using the dev release package from gisinternals.com (release-1600-x64-gdal-mapserver) and all is fine. I tested with one field, two fields, and a missing field (which correctly threw an error). Many thanks for this update.