Order By not working in union view

  • Thread starter Thread starter Doug Gifford
  • Start date Start date
D

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?
 
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
=========================================================
 
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

Doug Gifford said:
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
=========================================================
Doug Gifford said:
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
Click to expand...
sorted
by
[AcctNo]. (Appears to be sorted by CaseNo)

Why am I having this issue?
Click to expand...
Click to expand...
 
Thank you Karen,
Using SELECT * FROM dbo.xqryShowAcctNos ORDER BY AcctNo
worked!!!!

Thanks,
Doug

Karen Vaudreuil said:
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

Doug Gifford said:
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
=========================================================
Doug Gifford said:
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
Click to expand...
sorted
by
[AcctNo]. (Appears to be sorted by CaseNo)

Why am I having this issue?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top