To get max of top 10 records in SQL SERVER

Its not a problem but a very interesting doubt that I am having. I want to get the maximum of the top 10 defect id from the Defect table.
The Maximum defect id is 4062 and the Minimum is 139.

I am running the query

Code:

select top 10 defect_key from DEFECT_DIM

.

RESULT SET:
139
140
141
142
143
144
145
146
147
148

Now when I am running the query

Code:

select max(a.defect_key) from
(select top 10 defect_key from DEFECT_DIM)a

the RESULT is: 4062 and not the 148. Why this is coming ? Whereas from the top 10 defect id the maximum value should be 148.

More interestingly when I am running the query as:

Code:

select max(a.defect_key) from
(select top 10 defect_key from DEFECT_DIM
order by defect_key asc)a

The RESULT is: 148

Can you please tell me why the

Code:

select max(a.defect_key) from
(select top 10 defect_key from DEFECT_DIM)a

is not giving the expected result as 148 ?













[b]

try putting the "order by" in the first query, then comparing them

When you don't specify an ORDER BY SQL Server is free to order records however it sees fit. If there is an ascending clustered index often this will be used, but not always, and joins or other operations will affect what ordering it chooses.

Note that you can't really think of a subquery is as an intermediate result set--SQL Server doesn't necessarily finishing processing the subquery before doing the outer query.

Here's a way to get the results you want using a table variable (which does explicitly create an intermediate result set from which to pull out the max value):

DECLARE @defects TABLE (
defect_key int
)

INSERT INTO @defects
SELECT TOP 10 defect_key FROM DEFECT_DIM

SELECT MAX(defect_key) FROM @defects

By 'idividual DB' I assume you mean multiple schema residing on the same physical database server. There is no problem with this. If they are physically separate systems, then you will experience performance issues trying to do joins.