Re: Make ORDER BY in a view?

From: KeyStroke (Jack L. Swayze Sr.) <KeyStrk_at_Feist.Com>
Date: Thu, 10 Dec 1998 22:19:38 -0600
Message-ID: <36709D5A.7BB1B8DD@Feist.Com>

Since you have gotten the 'company line' from the other two who posted a reply, I will tell you how to do it, and not preach to you that you shouldn't (I have frequently thought that such 'preaching' is simply an indication that the person answering isn't willing to admit that they don't know how to do it).

the trick to all this is that GROUP BY will order the result, just as well as ORDER BY will. and you _CAN_ have a GROUP BY in a view!

Suppose a table has this structure:

the_table

---

pk1
pk2
nk1
nk1

and that pk1 and pk2 together (concatenated) form the primary key. Now suppose you want to create a view that shows the_table ordered by nk1 and nk2. Here is what you do:

create the_view as
select pk1, pk2, nk1, nk2 from
(select nk1, nk2, pk1, pk2, count(*) a_count   from the_table
  group by nk1, nk2, pk1, pk2
)
;

See, the trick is to have the primary key as part of the GROUP BY claus in the in-line view, then just strip away the count (which will always be the number '1') in the 'outer' select. If you don't have the primary key as part of the GROUP BY then you will be 'forgetting' some of the rows as they will be aggregated together (the count will be more than 1, but only one row will be returned).

suisum_at_ecn.ab.ca wrote:

> Hi:
>
> I know that we can't use ORDER BY in a view. Is there any way to get
> around this?
>
> --
> Best regards,
Received on Thu Dec 10 1998 - 22:19:38 CST