Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Post by Frickin Chicki » Fri, 14 Sep 2001 23:59:25



I have written a function that has the following overall structure:

SELECT x,y,z FROM
    functionX(...)

UNION

SELECT x,y,z FROM functionY(...)

ORDER BY x,y

(The function returns a TABLE )

The query works as expected when I run it in the query analyzer. However,
when I make a function out of it, the results are not sorted by fields x,y
as specified by the ORDER BY clause in the query.

Perhaps I should mention that x is a varchar and y is a smalldatetime.

(I tried creating a PK on the fields x,y in the function definition, but the
problem persists!)

Any ideas would be much appreciated!

Ryan

 
 
 

Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Post by lindawi » Sat, 15 Sep 2001 01:03:47


Ryan,

I'm afraid I'm not following you. Functions with a union works fine for me.
Please post some actual code and test data demonstrating the problem.



as begin



return
end
go


as begin



return
end
go
SELECT x,y,z FROM
    functionX('a')
UNION
SELECT x,y,z FROM functionY('b')
ORDER BY x, y

drop function functionX
drop function functionY

Linda


Quote:> I have written a function that has the following overall structure:

> SELECT x,y,z FROM
>     functionX(...)

> UNION

> SELECT x,y,z FROM functionY(...)

> ORDER BY x,y

> (The function returns a TABLE )

> The query works as expected when I run it in the query analyzer. However,
> when I make a function out of it, the results are not sorted by fields x,y
> as specified by the ORDER BY clause in the query.

> Perhaps I should mention that x is a varchar and y is a smalldatetime.

> (I tried creating a PK on the fields x,y in the function definition, but
the
> problem persists!)

> Any ideas would be much appreciated!

> Ryan


 
 
 

Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Post by Jason L » Sat, 15 Sep 2001 01:36:32


You couldn't and shouldn't use "order by" in inline functions or views. The
alternative is to use "order by" in your explict select query. For example,
you have a function fn_test which returns a table:
select lastname,firstname from fn_test order by lastname

Jason


Quote:> I have written a function that has the following overall structure:

> SELECT x,y,z FROM
>     functionX(...)

> UNION

> SELECT x,y,z FROM functionY(...)

> ORDER BY x,y

> (The function returns a TABLE )

> The query works as expected when I run it in the query analyzer. However,
> when I make a function out of it, the results are not sorted by fields x,y
> as specified by the ORDER BY clause in the query.

> Perhaps I should mention that x is a varchar and y is a smalldatetime.

> (I tried creating a PK on the fields x,y in the function definition, but
the
> problem persists!)

> Any ideas would be much appreciated!

> Ryan

 
 
 

Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Post by Griff » Sat, 15 Sep 2001 02:57:05


You have to do the order by on the first alias of the first SELECT
statement, for example:

(
    SELECT * From MyTable T1

    UNION

    SELECT * From MyTable T2
)
ORDER  BY T1.Field


Quote:> I have written a function that has the following overall structure:

> SELECT x,y,z FROM
>     functionX(...)

> UNION

> SELECT x,y,z FROM functionY(...)

> ORDER BY x,y

> (The function returns a TABLE )

> The query works as expected when I run it in the query analyzer. However,
> when I make a function out of it, the results are not sorted by fields x,y
> as specified by the ORDER BY clause in the query.

> Perhaps I should mention that x is a varchar and y is a smalldatetime.

> (I tried creating a PK on the fields x,y in the function definition, but
the
> problem persists!)

> Any ideas would be much appreciated!

> Ryan

 
 
 

Doing a straight SELECT * FROM function: the sorting is not consistent with the ORDER BY clause in the Function

Post by Steve Kas » Sat, 15 Sep 2001 08:25:54


A function that returns a table is not something you can "run in the
query analyzer," so can you explain exactly what it is that you are
running in the query analyzer, what you see, and what you expect
to see?  If myTableFunction(3) evaluates to a table, you cannot
"run the function" by executing

myTableFunction(3).

My suspicion is that you are executing a query like

SELECT x, y, z FROM myTableFunction(...)

and expecting the result set to be ordered in a
certain way (by x, y).  The only way to guarantee that
a SELECT statement yields an ordered result is with ORDER BY
in the SELECT statement.  Just because there's an ORDER BY
clause in the function definition doesn't mean that the table
will somehow be ordered in a certain way every time you
select rows from it (without an ORDER BY).

To get what you want, you need to run this query:

SELECT x, y, z FROM myTableFunction(...)
ORDER BY x, y

Steve Kass
Drew Universtiy


> I have written a function that has the following overall structure:

> SELECT x,y,z FROM
>     functionX(...)

> UNION

> SELECT x,y,z FROM functionY(...)

> ORDER BY x,y

> (The function returns a TABLE )

> The query works as expected when I run it in the query analyzer. However,
> when I make a function out of it, the results are not sorted by fields x,y
> as specified by the ORDER BY clause in the query.

> Perhaps I should mention that x is a varchar and y is a smalldatetime.

> (I tried creating a PK on the fields x,y in the function definition, but the
> problem persists!)

> Any ideas would be much appreciated!

> Ryan

 
 
 

1. Calling A Function (What am I doing wrong)

Ok, I just put a module in to check to see if a table exists.  Now, in my
code I want to call the function.... but I can't get my call statement
correct.  It keeps giving me errors no matter what I change it to....  My
function is declared as follows:

Function fExistTable(strTableName As String) As Integer

Shouldn't I call it by saying:

Call fexisttable(strtablename)    ????

Thanks!

2. applheapsz

3. What am I doing wrong with SELECT?

4. quick question on open_cursors parameter

5. I am not able to Sort ADO Hierarchical record set data

6. Get only operators from a column

7. A straight question, I need a straight Answer ?

8. LAST CHANCE! PLEASE RESPOND, CLIENT/SERVER SURVEY

9. Selecting ids where am/pm cols not null for whole week

10. Select not returning records based on Date I am using

11. Is non-read consistent select possible

12. what am I doing wrong?