Re: Two questions about views

Thomas J Kyte (tkyte_at_us.oracle.com) wrote:
: badri_at_cc.gatech.edu (badri) wrote:
:
: >Hi:
 

: >1) ORDER BY and views
: > I am creating a view and need to have an order by statement.
: >If I try it, it does not work. How do I make this to work?
:
: Order by does not work in views. A view is much like a table, it has no order.
: One way to trick it (a slow way) would be to use group by. Say you had a table
: and wanted a view that would order by a, b. You could:
:
: create view V
: as
: select a, b, c, d
: from T
: group by a, b, c, d
:
: The group by will force a sort. *Warning* I do not recommend doing this unless
: you will only ever be selecting from the View and not joining/etc this view to
: other objects. Even if you are just selecting from the view, this is not going
: to be as good as

No no no!! It will not just "force a sort". It will also strip out any duplicate records of a,b,c,d. Thus, your view will only be showing you distinct rows, not all rows.

If you need to order by, you will need to do so in the select statement that retrieves from the view.

Joe

-- 
                                   
=------------------------------------------------------------------=
Joe Nardone <joe_at_access.digex.net>                  
  "Me fail English?  That's unpossible!" - Ralph Wiggum

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: 2.6.2

mQBtAzCg5QoAAAEDANvEmc4OAT8wntnCv8UPjyD2kohKVGa1CU21PLpYljzl3AE7
pldpNNscKSoE//aVMKYBO+tRFTTsPNi0qZ6CDlEBXTgJAiszoHh1qCzZyYj7FTBL
YoUr+3sk9rU8Pma6AQAFEbQiSm9lIE5hcmRvbmUgPGpvZUBhY2Nlc3MuZGlnZXgu
bmV0Pg==
=1ocE
-----END PGP PUBLIC KEY BLOCK-----