SQL top 1 within WITH statement

I have a sql query which makes use of the WITH statement. The query looks like this:

WITH topAge as (
  select top 1 * from ages
  order by age
)

select * from topAge where ageGroup = 1

My question is whether the Where clause 开发者_Python百科gets executed after the top statement, because this query retrieves no records whereas I know that there are records in the database that should be retrieved.

Thanks in advance for any help.

---

The answer is: Yes, the ageGroup = 1 predicate is only applied after selecting top 1. Your query is equivalent to this

select * from (
  select top 1 * from ages
  order by age
) where ageGroup = 1

What you maybe want is this

select top 1 * from ages
where ageGroup = 1
order by age

---

This query:

select top 1 from ages order by age 

Does not actually select any fields, that's why it's not working, change it to:

SELECT TOP 1 age FROM ages ORDER BY age
--           ^^^