Why is ordering such a pain

I am, once again, faced with trying to apply an ORDER BY clause to a query.

The query in question is in my schema as a manually crafted SQL statement which joins several tables.
At the end of this I have the WHERE {WHERE} clause so I can use Dynamic Where on the client to filter the results.

Ordering the results is a totally different matter however.

Whilst it would seem I can use a Fill command in concert with a TableRequestInfoV5 structure and then leverage the Sorting property of this class to configure which field I need to sort on, this just doesn’t work at all.
I’m presuming this would only work with AutoSQL and, because I’ve created my own SQL, it has no way to know how to apply an ORDER BY clause to it?

Why can’t there be something like an ORDER BY {ORDER} to get round this, like with Dynamic Where?

What are my other options here?
I need to be able to control the ordering in response to user actions on the client, such as clicking a column header in a grid. It seems like the only option here is to pass a parameter and then construct some arcane CASE statement in the ORDER BY clause to control which column I order by.

Why is ordering so complex and non-functional? You did such a good job with the dynamic where functionality but ordering seems to have been completely forgotten. Why isn’t there a similar way of programmatically constructing an ORDER BY clause in XML which the server then uses when constructing the SQL?

I think it’s a good feature idea, and something we could look at adding, yes.

That said, the thought behind this not being there is that whole filtering on the server side is an important functionality because you do not want to download data you don’t need from the server, ordering is really irrelevant because once you have the data on the client, you can order and re-order it any way yo please, any number of times — client side. In fact, in most clases data would be displayed in something such as a grid, where (usually) the user can reorder on the fly, as a client0side operation.

IOW, Filtering is a data retrieval issue. Ordering is a display issue.

Hi,

You can use the DA SQL feature and the RDA.FillWithDASql method.

in DA SQL you can specify any ordering:

SELECT * FROM customers ORDER BY CompanyName
SELECT * FROM customers ORDER BY CID DESC
SELECT * FROM customers ORDER BY ContactPerson ASC
SELECT customerid, firstname, lastname FROM customers ORDER BY 3 DESC
SELECT OrderID, ItemID, Price, Qty, Discount FROM [Order Details] ORDER BY OrderID, Price DESC

SELECT c.cid, o.id, o.createdate, freight
  FROM Customers c
    INNER JOIN orders o ON c.cid = o.customer
  WHERE c.cid = 'ALFKI'
  ORDER BY o.createdate, freight DESC

Logged as bugs://D19336.

Thanks both.

The thing is, ordering can also be about filtering.

For example, say I have a table of orders which has hundreds of thousands of rows.
I want to retrieve the top 100 largest orders of all time.

I’d want to execute something along the lines of SELECT TOP 100 * FROM Orders ORDER BY Value DESC

Granted this is a very simple example and easily achieved but it demonstrates how you can sometimes “filter” the results using a combination of ORDER BY and TOP to get the “most” or “least” or “highest”.

In our system, we are often required to provide a search interface where the underlying tables could have hundreds of thousands of records. Obviously we only retrieve the “TOP x” records in such cases to keep performance up. The user can provide a range of search criteria which is converted into parameters and/or dynamic where but can also use the grid column headers to simply order the results to get the “most”, “least”, “recent” etc.

Hi,

as a temporary workaround, you can use DA SQL.
check the Supported SQL Syntax article

Yes, I’ve never tried DA SQL, primarily as it wasn’t available in Delphi servers until relatively recently, but is something I’m planning on looking at for some of our more complex queries.

1 Like

Hi,

DA SQL feature was added to Delphi two years ago.

I can recommend to review DA SQL sample (Delphi) with DA Server sample (Delphi)

bugs://D19336 was closed as fixed.

Hi,

Is there any more detail of exactly what’s been fixed/added here as I’m very interested.

Thanks.

Hi,

check Data Abstract for .NET vNext: New features topic

Cool thanks.

1 Like

Hi,

we released preview build with this feature

check more at the Dynamic Order article

1 Like

Thank you.

1 Like