Re: Sorted views (was: Sort order change from Mainframe to

Robert:
Experts in the database field advise against relying on ordering of the =
yield of views. Date cites an example. An ordering by one attribute does =
not have one possible result for any one source dataset. As a result, =
tests of queries may succeed in one context, given an ordered view, but =
fail in another context given that same view. More generally, since SQL =
does not require pre-ordering of tuples, nor guarantee that processing =
will maintain a sort order, it usually makes more sense to order data =
within data display procedures than in a view. I can think of =
exceptional situations, but each situation would have to be considered =
carefully.
S

________________________________

From: owner-sas-l@listserv.uga.edu on behalf of Robert Bardos
Sent: Sun 4/1/2007 3:36 AM
To: SAS-L List
Subject: Sorted views (was: Sort order change from Mainframe to Windows)



Howard Schreier's post in the "Sort order change from Mainframe to
Windows" thread contains a very interesting aspect regarding views
and sort:

> Howard Schreier

<some lines snipped ... RB>

> Boston, with an upper case "B", would appear before
> "atlanta" in a simple sort.
>
> So create a view with "hidden" sort keys:
>
> proc sql;
> create view temp_in_report_order as
> select *
> from temp
> order by anydigit(substr(region,1,1) )
> , upcase(region)
> , region;
> quit;
>
> The first ORDER key pushes the digit strings to the
> bottom. The second
> ignores case.
>
> The view looks like this:
>
> region runame
>
> atlanta atlanta
> Boston Boston
> dallas dallas
> newyork newyork
> 100 100
> 500 500
>
> Then code:
>
> proc print data=3Dtemp_in_report_order;
> by region notsorted;
> run;
>

Learned something new today (though I could/should have known it).
Quite often I refrained from using views since "you can't create
sorted views" (meaning: "you can't sort views onto themselves" or
even "you can't create a view as output from proc sort"). I was
wrong: while I can't use datastep views for that purpose I can
very well use SQL views for that purpose!

Knowledge beats experiments here (as most everybody who tried to
develop fool-proof code knows, the universe will win this contest,
so even intensive experimenting may miss exactly the one case
where my assumptions go wrong), so I ask this august group: would
you consider the use of sorted SQL views safe for production jobs
("jobs" in the sense of "batch SAS executions") ??

TIA for your thoughts

Robert Bardos
Ansys AG, Zurich, Switzerland