BenSCooper
Programmer
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
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