CODEWITHSUNDEEP
sqlsql-server
Jebathon
Jebathon

Reputation: 4561

SQL Server - Create View with rows from multiple result sets

Given the following queries

Select Count(*) From TableA

Select Count(*) From TableB

Select Count(*) From TableC

Select Count(*) From TableD

I want to return a view like:

-----------
| Counts  |
-----------
| 2       | --from TableA
-----------
| 3       | --from TableB
----------
| 4       | --from TableC
----------
| 5       | --from TableD
----------

I tried using UNION e.g:

CREATE VIEW [Foo]
AS
SELECT COUNT(*) AS [Counts]
FROM TableA
UNION
SELECT COUNT(*)
FROM TableB
UNION
SELECT COUNT(*)
FROM TableC
UNION
SELECT COUNT(*)
FROM TableD

But the order was incorrect

-----------
| Counts  |
-----------
| 2       | --from TableA
-----------
| 3       | --from TableB
----------
| 5       | --from TableD
----------
| 4       | --from TableC
----------

Is there an alternative besides UNION I can use to produce the result set I want?

Upvotes: 0

Views: 231

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Views return unordered result sets. You could try to do:

create view v_counts as
    select . . .
    order by . . .

And you'll get the error message:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

As the message suggests, there is a way to cheat to get around this, but I advise against it -- even when you cheat, the view is not guaranteed to return results in order.

Instead, include the table name:

Select 'TableA' as which, Count(*) From TableA
union all
Select 'TableB' as which, Count(*) From TableB
union all
Select 'TableC' as which, Count(*) From TableC
union all
Select 'TableD' as which, Count(*) From TableD;

Upvotes: 1

Ross Bush
Ross Bush

Reputation: 15175

Very close, just add a derived field for your order.

CREATE VIEW [Foo]
AS
SELECT TOP(100) PERCENT
*
FROM
(
    SELECT COUNT(*) AS [Counts], RowOrder=1
    FROM TableA
    UNION
    SELECT COUNT(*), 2
    FROM TableB
    UNION
    SELECT COUNT(*), 3
    FROM TableC
    UNION
    SELECT COUNT(*), 4
    FROM TableD
)AS X
ORDER BY RowOrder

OR - Order outside of the count view.

CREATE VIEW [Foo]
AS
SELECT 
*
FROM
(
    SELECT COUNT(*) AS [Counts], RowOrder=1
    FROM TableA
    UNION
    SELECT COUNT(*), 2
    FROM TableB
    UNION
    SELECT COUNT(*), 3
    FROM TableC
    UNION
    SELECT COUNT(*), 4
    FROM TableD
)AS X

...
SELECT * FROM Foo ORDER BY RowOrder

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

UNION internally does the sorting for you, you need union all or define explicit ordring instead :

SELECT COUNT(*) AS [Counts]
FROM TableA UNION ALL
SELECT COUNT(*)
FROM TableB UNION ALL
SELECT COUNT(*)
FROM TableC UNION ALL
SELECT COUNT(*)
FROM TableD;

You can also define explicit ordering :

SELECT COUNT(*) AS [Counts], 1 as SortOrder
FROM TableA UNION 
SELECT COUNT(*), 2
FROM TableB UNION 
SELECT COUNT(*), 3
FROM TableC UNION 
SELECT COUNT(*), 4
FROM TableD;

So, you need to use order by clause in view Foo

select f.*
from Foo f
order by SortOrder;

Upvotes: 1

Related Questions