CODEWITHSUNDEEP
sql-servert-sql
Richard Schaefer
Richard Schaefer

Reputation: 605

Query against subquery yields different results than running subquery alone

I have a query that uses a subquery to subset the data, then I'm trying to select specific data from the subquery. The subquery is:

select top 10 Build_ID, Appscan_Definitive_High,
rank() over (order by Appscan_Definitive_High desc) as rankpct
from 
(
select build_id, convert(int,appscan_definitive_high) as
           appscan_definitive_high
from dbo.SDFBuildMetrics
where coalesce(appscan_definitive_high,0)>0
) a

and the results from this are:

Build_ID    Appscan_Definitive_High rankpct
31966   51  1
32627   51  1
44293   51  1
47011   51  1
47968   51  1
48554   51  1
25586   49  7
27370   49  7
40357   48  9
23867   44  10

But when I run a query against the subquery:

select Appscan_Definitive_High
from
(
select top 10 Build_ID, Appscan_Definitive_High,
    rank() over (order by Appscan_Definitive_High desc) as rankpct
from 
(
    select build_id, convert(int,appscan_definitive_high) as
                appscan_definitive_high
    from dbo.SDFBuildMetrics
    where coalesce(appscan_definitive_high,0)>0
) a 
 ) aa

I get:

Appscan_Definitive_High
1
44
21
44
2
44
2
6
7
7

The ultimate intent of the full query is to retrieve min(AppScan_Definitive_High) but since the set of values being returned doesn't match the set of values returned from the subquery the min function doesn't give me what I need. I assume the subquery returns a set of data that the outer query operates against, but this appears to not be the case in the above example.

Any help with this?

Upvotes: 0

Views: 66

Answers (1)

James Z
James Z

Reputation: 12317

You're running select top 10 without order by. To get the result you want you need to have order by there.

Upvotes: 5

Related Questions