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.
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
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.
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.