Make ORDER BY in a view?
sui...@ecn.ab.ca
I know that we can't use ORDER BY in a view. Is there any way to get
around this?
--
Best regards,
Paweł Plichta
You must create view without ORDRE BY and use it when you ask database ( in
client application )
sui...@ecn.ab.ca napisał(a) w wiadomości: <366fc...@ecn.ab.ca>...
KeyStroke (Jack L. Swayze Sr.)
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).
Andrew Forsyth
sui...@ecn.ab.ca wrote in message <366fc...@ecn.ab.ca>...
>Hi:
>
>I know that we can't use ORDER BY in a view. Is there any way to get
>around this?
>
order by Col;
But seriously, imagine if you *could* "order by" in view VA:
CREATE VIEW VA AS SELECT X, Y FROM ... ORDER BY X;
OK, now create another view
CREATE VIEW VB AS SELECT X, Y FROM VA ORDER BY Y;
and another view VC, ordered by X again, and so forth. This would be
entirely logical, but would keep the sort algorithm merchants in business
big time (first sort the result set of VA by X, then pass it to VB and sort
it by Y, and then ...)
Really you only need to sort at the very last moment. So use a SELECT
statement, not a VIEW.
I've wanted sorted views too from time to time but the implications are a
bit difficult.
Hope this makes sense.
Matthias Gresz
>
> Hi:
>
> I know that we can't use ORDER BY in a view. Is there any way to get
> around this?
>
> Best regards,
One thing that may help you is to add a group by clause to your
statement:
CREATE VIEW emp_test as
SELECT
ENAME,
EMPNO
FROM
EMP
GROUP BY
ENAME,
EMP;
GROUP BY makes an implicit sort.
HTH
Matthias
--
Matthia...@Privat.Post.DE
Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm
Mungo Henning
> 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!
Whilst not contradicting this nice work-around, how stable is the
postulated solution?
As I (badly?) understand it, "group by" clusters records according to
some condition. The clustering may be implemented by sorting, but it
needn't always be so. Hence the separate "group by" and "order by"
clauses.
What would happen if a new "group by" mechanism were to be invented
a few weeks after you've rolled out your application and you had
assumed that "group by" performed an "order by"...
Whether this is likely is your call, but personally I'd be cautious
of such effects lest they disappear from under my feet in a future
version.
As another poster said, one ultimate ORDER BY may be all that is
necessary.
Mungo Henning
Grt
You can force a order by into a view by using UNION (it has an implicit order
by):
select first_preferred_column, next_preferred_column, third_preferred_column
from my_table
UNION
select first_preferred_column, next_preferred_column, third_preferred_column
from my_table;
I presume it will take some extra processing...
Grt
KeyStroke (Jack L. Swayze Sr.)
change the code in the system that is issuing the original SELECT statement,
then you have to take measures in the database to accomplish what the user
really wants.
--
You can email me by removing the _NO_SPAM_ in the email address. Make sure you
remove all three underscores.
Thomas Kyte
(if that email address didn't require changing)
On Thu, 10 Dec 1998 22:19:38 -0600, you wrote:
>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).
read on before...
>
>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).
>
I would recommend not relying on GROUP BY to SORT under any circumstances.
Simple things such as indexes, optimizer modes, degree of parallism, partitions,
and table organization will render a GROUP BY useless as a sorting device (never
mind the performance implication of sorting the entire table before any
predicates could be applied -- every time any queries through a view of the
above type, with the group by, the group by must be done BEFORE any predicates
the application puts on the view are done -- every time). If your application
depends on the data being sorted, you had better sort it.
I will simply post 2 counter examples to the above and make no other comments.
--------------- example 1 --------------------------------------
SQL> create table the_table ( pk1 int,
2 pk2 int,
3 nk1 int not null,
4 nk2 int not null,
5 primary key(pk1,pk2) )
6 /
Table created.
SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2)
2 /
Index created.
SQL> insert into the_table values (1, 1, 2, 2 );
1 row created.
SQL> insert into the_table values (2, 1, 1, 1 );
1 row created.
SQL> analyze table the_table compute statistics;
Table analyzed.
SQL> set autotrace on explain
SQL> select nk1, nk2, pk1, pk2, count(*) a_count
2 from the_table
3 group by nk1, nk2, pk1, pk2
4 ;
NK1 NK2 PK1 PK2 A_COUNT
---------- ---------- ---------- ---------- ----------
2 2 1 1 1
1 1 2 1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=2 Bytes=30)
1 0 SORT (GROUP BY NOSORT) (Cost=1 Card=2 Bytes=30)
2 1 INDEX (FULL SCAN) OF 'THE_TABLE_IDX' (NON-UNIQUE) (Cost=
1 Card=2 Bytes=30)
-- end result -- data not sorted by NK1, NK2.
------------------ example 2 ------------------------------------
SQL> create table the_table ( pk1 int,
2 pk2 int,
3 nk1 int,
4 nk2 int,
5 primary key(pk1,pk2) )
6 organization index
7 /
Table created.
SQL>
SQL> insert into the_table values (1, 1, 2, 2 );
1 row created.
SQL> insert into the_table values (2, 1, 1, 1 );
1 row created.
SQL>
SQL> set autotrace on explain
SQL>
SQL> select nk1, nk2, pk1, pk2, count(*) a_count
2 from the_table
3 group by nk1, nk2, pk1, pk2
4 ;
NK1 NK2 PK1 PK2 A_COUNT
---------- ---------- ---------- ---------- ----------
2 2 1 1 1
1 1 2 1 1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=104)
1 0 SORT (GROUP BY NOSORT) (Cost=28 Card=1 Bytes=104)
2 1 INDEX (FULL SCAN) OF 'SYS_IOT_TOP_80023' (UNIQUE) (Cost=
26 Card=1 Bytes=104)
---- end result -- data is not sorted by NK1, NK2
>sui...@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,
Thomas Kyte
tk...@us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you,
you have to make it easy to get email to you. Any bounced
email will be treated the same way i treat SPAM-- I delete it.
Thomas Kyte
(if that email address didn't require changing)
>KeyStroke (Jack L. Swayze Sr.) wrote:
>
>> 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!
>
>Whilst not contradicting this nice work-around, how stable is the
>postulated solution?
Not very stable at all actually. Influences such as INDEXES, Partitions,
Parallel Query, NULL/NOT NULL constraints, OPTIMIZER mode and others will affect
it.
>As I (badly?) understand it, "group by" clusters records according to
>some condition. The clustering may be implemented by sorting, but it
>needn't always be so. Hence the separate "group by" and "order by"
>clauses.
EXACTLY. Well said. That is 100% correct. You understand well actually.
>What would happen if a new "group by" mechanism were to be invented
>a few weeks after you've rolled out your application and you had
>assumed that "group by" performed an "order by"...
You don't even need to wait a few weeks -- don't even need to wait 1 day. its
ALWAYS been that way.
There are 2 GROUP functions for GROUP BY:
- GROUP BY
- GROUP BY NOSORT
>Whether this is likely is your call, but personally I'd be cautious
>of such effects lest they disappear from under my feet in a future
>version.
They have already disappeared. Here is an example from 7.1 (taking you back to
1994)
SQL> create table emp as select * from scott.emp where rownum <= 5;
Table created.
SQL> create index emp_idx on emp(job,ename);
Index created.
SQL> select ename, job from emp where ename >chr(0) and job >chr(0)
2 group by ename, job ;
ENAME JOB
---------- ---------
smith CLERK
jones MANAGER
allen SALESMAN
martin SALESMAN
ward SALESMAN
group by ename,job != order by ename, job. Here the optimizer used an index to
get the answer and realizing the Group by ename, job was the same as group by
job, ename -- did it that way. think about what the effect on your application
would be if the emp_idx was added AFTER your application went production.
In short -- anyone who relies on the order of rows in a SQL statement for their
processing and does not use ORDER BY is doing the wrong thing.
>As another poster said, one ultimate ORDER BY may be all that is
>necessary.
>
not only "all that is neccessary" -- its the only thing that works reliably and
all of the time...
Here is another small example that doesn't even need the where clause on a
table. All that happened was someone analyzed the table. All of a sudden, the
group by that sorted -- stops sorting!
SQL> create table the_table ( pk1 int,
2 pk2 int,
3 nk1 int,
4 nk2 int,
5 primary key(pk1,pk2) )
6 /
Table created.
SQL> create index the_table_idx on the_table(pk1,nk1,pk2,nk2);
Index created.
SQL> insert into the_table values (1, 1, 2, 2 );
1 row created.
SQL> insert into the_table values (2, 1, 1, 1 );
1 row created.
SQL> select nk1, nk2, pk1, pk2, count(*) a_count_1
2 from the_table
3 group by nk1, nk2, pk1, pk2;
NK1 NK2 PK1 PK2 A_COUNT_1
---------- ---------- ---------- ---------- ----------
1 1 2 1 1
2 2 1 1 1
SQL> analyze table the_table compute statistics;
Table analyzed.
SQL> select nk1, nk2, pk1, pk2, count(*) a_count_2
2 from the_table
3 group by nk1, nk2, pk1, pk2;
NK1 NK2 PK1 PK2 A_COUNT_2
---------- ---------- ---------- ---------- ----------
2 2 1 1 1
1 1 2 1 1
>Mungo Henning
Mungo Henning
>
> change the code in the system that is issuing the original SELECT statement,
> then you have to take measures in the database to accomplish what the user
> really wants.
Methinks the saying "when needs must the devil provides" applies here. If you
are
under pressure to "solve the problem" in the scene which you depict then I guess
you have two stark choices:
1) Tell your boss that it cannot be done without a change on the "other side"
2) Implement a "dirty" fix and tell your boss that its not guaranteed to work
You may not have the luxury of choice one. You may therefore have to take choice
two. Whether choice two is also accompanied by telling your boss how cr*ppy the
fix is is down to professionalism/personalities and a raft of other issues.
Having browsed over the "simmering" posts on this thread I can see both sides (I
think).
I understand the resentment from those at the "front line" when an application
doesn't
support a particular feature which would make front-line life less like
disorganised hell.
Those in the production factories are also right in their wish to keep the model
clean
and not degenerate into a melee of minor features.
It is true that if manufacturers consistently fail to provide adequate features
then
people will vote with their feet, but it is wrong to depict a unreliable 'fix'
as anything
less than reliable - the "group by" would perhaps be all that the thread
originator needs,
dependent on the platform/tables and other ingredients, but it won't work in
every
circumstance.
I still think that without adequate warnings of the "reliability" of the 'fix'
then
the information broadcast is dangerous to those "not in the know".
Were I to use an Oracle feature within its advertised remit and it failed to
work
then I would be on the telephone rapidly to get a fix from Oracle. Were I to use
a
feature outwith its remit and it failed then I have no moral right of complaint
(IMHO). I may have a right to criticise the supplier, but since I've moved out
of
the "acceptable use" envelope then all my rights evaporate.
Interesting viewpoints from both sides: can I request a reduction in vehemence
please?
Thanks in advance
Mungo Henning
P.S. To lighten the mood, here's a little apocryphal story.
A farmer was told by the government that a huge gas pipeline (crossing the
country) was
to be laid across one of his fields.
No sooner had the work started when one night a pair of shady characters arrived
at the
farmer's door.
"How would you like free gas for life?" asked one of the characters.
"How?" asked the farmer.
"We'll tap into that huge pipeline for you: they'll never miss a small amount
of gas with the volume flowing through there" said the shady character.
"How much?" asked the farmer
"Two thousand" replied the characters.
"Agreed" said the farmer.
A few days later the shady characters have laid a small pipe up from the
field into the farm. An hour later they call the farmer to inspect the
work: there is gas emanating from the pipe at the farm.
With mischievious delight, the farmer paid the two characters who then
bade him farewell and scarpered.
Two weeks later, the "free" gas supply stopped, and without any way to
contact the shady characters the farmer investigated himself.
He traced the pipeline from the farm into the field, and near to the
main gas pipeline he unearthed two substantial gas cylinders... the
characters hadn't tapped the main pipe after all.
Dilemma time: who does the farmer complain to?
You may see a connection between the farmer and anyone who uses a Group By
to perform a surreptitious sort... :-)