How to get effect of sorted database view?

Question

I'd like to be able to (effectively) sort a database view - I know that conceptually order in a db view is invalid, but I have the following scenario to deal with:

• a third-party legacy application, that reads data from database tables using a select(*) from tablename statement
• the legacy application is very sensitive to the order of the records
• an application I've written to allow users to manage the data in the tables more easily, but inserts and deletes from the table naturally upset the order of the records.

Changing the statement in the legacy application to select (*) from tablename order by field would fix my problem, but isn't an option.

So - I've set up a staging table into which the data can be exported in the right order, but this is a resource-hungry option, means that the data isn't 'live' in the legacy application and is additional work for users.

I'd like to be able to get at an ordered version of the table with these contraints. Any ideas how?

---

Update - I'm working with Sybase 12.5, but I'd like to avoid a tightly coupled solution with a specific RDBMS - it might change.

I cannot add an 'order by' clause to a view, because of SQL standards as referred to in this Wikipedia entry

Answer 1

It's not nice, but it works

CREATE VIEW OrderedTable
AS SELECT TOP (Select Count(*) from UnorderedTable) *
FROM UnorderedTable Order By field

Answer 2

If I'm understanding this correctly, you could solve this by:
1) Renaming the original table
2) Creating a view with the name of the table that the legacy app queries.
3) Define the view to be a query that orders the records in the way that legacy app expects.

Answer 3

First off, I've had to work on this type of project before and it's truly a bitch. My condolences.

This is a little out there, but if your DBMS supports it, perhaps you could create a user defined table function that does an ordered select from your legacy table, then set up your view to select from the UDTF. It's not anything I've ever done before though.

Answer 4

You might try a table-valued function. You didn't specify your database vendor, but here's how you would do it in TSQL (Sql Server):

CREATE FUNCTION orderedTable() 
RETURNS @returnTable TABLE 
    (val varchar(100)) AS
BEGIN
    insert @returnTable (val)
    select val from MyTable
    order by val desc
    RETURN 
END

GO

SELECT * FROM orderedTable

Answer 5

In MS Sql Server, we can bastardize the standards and create a view such as:

SELECT TOP 100 PERCENT * FROM TABLE ORDER BY 1

which lets us get around issues like this. Since Sybase and Sql Server share T-SQL, I'd think there's a good chance you could do that as well.

Alternatively, you can set a clustered index on the field that it should be ordered by. This will force storage order, which will* return in that as "natural order".

• This is, admittedly, an implementation detail. The SQL standards (nor any specific vendor, AFAIK) don't guarantee ordering without an order by clause...but, hey, if you could use that, then you wouldn't be asking.

Answer 6

Following up on the info you guys have provided, looks like I can't do what I want to on Sybase ASE 12.5.

MSSQL Server and Sybase ASE 15.x should do what's needed - hopefully I'll be able to arrange something. Not really sure which one to accept til I've got something working, but I'll come back and accept an answer then.