CODEWITHSUNDEEP
sqlt-sqlviewdistinct
tdjfdjdj
tdjfdjdj

Reputation: 2471

view results differ from same code as query

I have a simple select with one inner join query. However when I create a view with the same code, the results are different. I am not pulling data from views. It is all user table based.

QUERY:

SELECT DISTINCT TOP 100 
dbo.table1.a1,
dbo.table1.a2,
dbo.table2.something_else  FROM dbo.table1 inner join 
dbo.table2 ON 
dbo.tabel1.a1 = dbo.table2.somethingelse
WHERE dbo.table.a2 = '1'
ORDER BY dbo.table.a1 DESC

VIEW:

CREATE VIEW TEST
AS

SELECT DISTINCT TOP 100 
dbo.table1.a1,
dbo.table1.a2,
dbo.table2.something_else  FROM dbo.table1 inner join 
dbo.table2 ON 
dbo.tabel1.a1 = dbo.table2.somethingelse
WHERE dbo.table.a2 = '1'
ORDER BY dbo.table.a1 DESC

When I compare the results of the Query to the View, they appear to be different. The VIEW has more result then the query and the order is not the same (due to more records in the VIEW than query.) Its the same code, so how can it be different?

Upvotes: 2

Views: 327

Answers (2)

Quassnoi
Quassnoi

Reputation: 425523

I'm assuming you're talking about this query:

SELECT  DISTINCT TOP 100 PERCENT
        dbo.table1.a1,
        dbo.table1.a2,
        dbo.table2.something_else
FROM    dbo.table1
JOIN    dbo.table2
ON      dbo.tabel1.a1 = dbo.table2.somethingelse
WHERE   dbo.table.a2 = '1'
ORDER BY
        dbo.table.a1 DESC

In this case, SQL Server just optimizes away the ORDER BY part when running the view.

You should explicitly add it to the query that calls the view:

SELECT  *
FROM    test
ORDER BY
        a1 DESC

Upvotes: 2

Clockwork-Muse
Clockwork-Muse

Reputation: 13086

Generally speaking, ORDER BY and related statements are not allowed in view creation. Simply put, the results are returned in an undefined order from a view. This is probably why you're also not restricting to the top 100 rows, because the rows aren't ordered.

Pull out your ordering and rowcount into the statement that selects from the view, and see if that works.

Upvotes: 4

Related Questions