view, union and order by

[DLu]

I have a view with union 3 tables, how could I put order by within the view at the bottom?

I also tried select 100 percent ..... , but order by with error...

thanks
David

[rmiao]

I think should order results when you query the view.

[DLu]

H Rmiao:

I think I found the solution:

select top 100 percent * from (select ...union...select...) as x
order by column1

it works to break the barrier of sql2000 limitaion..

thanks
David

[DLu]

Hi Ramio:

select * from vw_ABC -->works fine with correct order by.
but failed in run the application where 37 procs call this view with time-out.

It looks that the procs call this view is kind of confusing about the select top 100 * from (select a, b, c from ..... union select x, y, z from ....) as X oder by A.

-D

[shaggy]

The view don't accept the order by clausula, you can use it when you call the view

Greetings,
Santiago Carela
Rep. Dom.

[skhanal]

ANSI standard prohibits ORDER BY in view definition, the workaround you have works but it may not be related to timeouts you have in application. The resultset may be too big for application to handle.

[PABluesMan]

One thing you can try that doesn't require all that mucking about with TOP 100 PERCENT etc. etc. is to specify the column ordinals. Here's a sample:

/*************************************/
SELECT Col1, Col2, Col3, Col4, Col5
FROM Table1
UNION ALL
SELECT Col1, Col2, Col3, Col4, Col5
FROM Table2
ORDER BY 3, 2, 5
/*************************************/

Hope this helps!