Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Order By In Table Function

Status
Not open for further replies.

BenSCooper

Programmer
Sep 28, 2009
15
GB
Dear All,
Please can someone explain why the results of the following table type function in SQL Server 2005 don't sort as per the ORDER BY statement.

ALTER FUNCTION [dbo].[CONTACT_HISTORY_UNION]
(
@ContactID AS int
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 100 PERCENT *
FROM (
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE], CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_Cars_Credited] (@ContactID)
UNION
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE], CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_CarsSold_Financed] (@ContactID)
UNION
SELECT ID, Vehicle, CAR_SALES_ID, INVOICE_ID, InvoiceDate, Detail, [TYPE],CAR_SALES_ID as JOBNO
FROM [dbo].[CONTACT_HISTORY_CarsSold] (@ContactID)
) uni
ORDER BY INVOICE_ID
)

The select carries out the union on the three functions correctly, but applies no sortation on the results.

Does anyone know why this may be?

Many Thanks
Ben Cooper
 
Why you need to order by in Function?
Order that table there where you need it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Take a look at Brad's comment in this thread
--------------------------
TOP 100 PERCENT is not guaranteed to work. It's a kludge that people used to use to make sure views came out in order, but starting in SQL2005 (I believe), the optimizer most likely throws it out the window... we can't fool it anymore.

PluralSight Learning Library
 
Thanks guys, I guess I'll just have to order on the call expression.

Cheers
Ben Cooper
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top