Order By not working in union view

[Doug Gifford]

Access XP ADP
SQL server 2000


ALTER VIEW dbo.qryShowAcctNos
AS
SELECT TOP 100 PERCENT mnyAccounts.CaseNo,
cast([mnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'A' AS Source
FROM mnyAccounts
UNION ALL
SELECT TOP 100 PERCENT rmnyAccounts.CaseNo,
cast([rmnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'R' AS Source
FROM rmnyAccounts
ORDER BY [AcctNo]

When running the Union - Order By in Query Analyzer the recordset is sorted
correctly.

When selecting and opening the View, the returned recordset is not sorted by
[AcctNo]. (Appears to be sorted by CaseNo)

Why am I having this issue?

 

[Doug Gifford]

Also tried the following with the same results! The view does not return
the recordset sorted by AcctNo!
======
create view xqryShowAcctNos
AS
SELECT top 100 percent mnyAccounts.CaseNo,
[mnyAccounts].[AccountNumber] AS [AcctNo], 'A' as Source
FROM mnyAccounts

UNION ALL

SELECT top 100 percent rmnyAccounts.CaseNo,
[rmnyAccounts].[AccountNumber] AS [AcctNo], 'R' as Source
FROM rmnyAccounts

ORDER BY AcctNo
=========================================================

 

[Karen Vaudreuil]

Hi Doug,

It's not possible to use ORDER BY in views. In your case, you can specify
it because you use TOP, but it's only used by SQL Server to determine which
rows will be selected. The view won't be sorted. I suggest you use a
stored procedure instead, if possible, or you can use your view in the
recordsource of forms and reports this way: SELECT * FROM
dbo.xqryShowAcctNos ORDER BY AcctNo.

Karen

Also tried the following with the same results! The view does not return
the recordset sorted by AcctNo!
======
create view xqryShowAcctNos
AS
SELECT top 100 percent mnyAccounts.CaseNo,
[mnyAccounts].[AccountNumber] AS [AcctNo], 'A' as Source
FROM mnyAccounts

UNION ALL

SELECT top 100 percent rmnyAccounts.CaseNo,
[rmnyAccounts].[AccountNumber] AS [AcctNo], 'R' as Source
FROM rmnyAccounts

ORDER BY AcctNo
=========================================================

Access XP ADP
SQL server 2000


ALTER VIEW dbo.qryShowAcctNos
AS
SELECT TOP 100 PERCENT mnyAccounts.CaseNo,
cast([mnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'A' AS Source
FROM mnyAccounts
UNION ALL
SELECT TOP 100 PERCENT rmnyAccounts.CaseNo,
cast([rmnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'R' AS Source
FROM rmnyAccounts
ORDER BY [AcctNo]

When running the Union - Order By in Query Analyzer the recordset is sorted
correctly.

When selecting and opening the View, the returned recordset is not

sorted

by
[AcctNo]. (Appears to be sorted by CaseNo)

Why am I having this issue?

 

[Doug Gifford]

Thank you Karen,
Using SELECT * FROM dbo.xqryShowAcctNos ORDER BY AcctNo
worked!!!!

Thanks,
Doug

Hi Doug,

It's not possible to use ORDER BY in views. In your case, you can specify
it because you use TOP, but it's only used by SQL Server to determine which
rows will be selected. The view won't be sorted. I suggest you use a
stored procedure instead, if possible, or you can use your view in the
recordsource of forms and reports this way: SELECT * FROM
dbo.xqryShowAcctNos ORDER BY AcctNo.

Karen

Also tried the following with the same results! The view does not return
the recordset sorted by AcctNo!
======
create view xqryShowAcctNos
AS
SELECT top 100 percent mnyAccounts.CaseNo,
[mnyAccounts].[AccountNumber] AS [AcctNo], 'A' as Source
FROM mnyAccounts

UNION ALL

SELECT top 100 percent rmnyAccounts.CaseNo,
[rmnyAccounts].[AccountNumber] AS [AcctNo], 'R' as Source
FROM rmnyAccounts

ORDER BY AcctNo
=========================================================

Access XP ADP
SQL server 2000


ALTER VIEW dbo.qryShowAcctNos
AS
SELECT TOP 100 PERCENT mnyAccounts.CaseNo,
cast([mnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'A' AS Source
FROM mnyAccounts
UNION ALL
SELECT TOP 100 PERCENT rmnyAccounts.CaseNo,
cast([rmnyAccounts].[AccountNumber] AS bigint) AS [AcctNo], 'R' AS Source
FROM rmnyAccounts
ORDER BY [AcctNo]

When running the Union - Order By in Query Analyzer the recordset is sorted
correctly.

When selecting and opening the View, the returned recordset is not

sorted

by
[AcctNo]. (Appears to be sorted by CaseNo)

Why am I having this issue?