To get max of top 10 records in SQL SERVER

View previous topic View next topic Go down

To get max of top 10 records in SQL SERVER

Post  sudip.bandyopadhyay on Fri Jul 01, 2011 6:36 am

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]

sudip.bandyopadhyay

Posts : 3
Join date : 2011-03-15

View user profile

Back to top Go down

Re: To get max of top 10 records in SQL SERVER

Post  EvanJones on Sat Jul 23, 2011 8:51 am

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

EvanJones

Posts : 4
Join date : 2011-07-09
Location : Southern NH

View user profile

Back to top Go down

Re: To get max of top 10 records in SQL SERVER

Post  VHF on Tue Jul 26, 2011 10:33 am

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






VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: To get max of top 10 records in SQL SERVER

Post  miloson24 on Thu Apr 16, 2015 6:49 am

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.

miloson24

Posts : 1
Join date : 2015-04-16

View user profile

Back to top Go down

Re: To get max of top 10 records in SQL SERVER

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum