rank over query

trying to use rank over partition with a join and am failing.  the following query works but when i try to only select the rows where the rank is 1, it doesnt recognize dog as a column

the working querry
select RANK () OVER (PARTITION BY B.ACCOUNTKEY ORDER BY B.ADDDTTM DESC) AS DOG ,* from billing.account A
INNER JOIN BILLING.BILL B
ON A.ACCOUNTKEY = B.ACCOUNTKEY

the query that doesnt work and returns dog as an unrecognized column
select RANK () OVER (PARTITION BY B.ACCOUNTKEY ORDER BY B.ADDDTTM DESC) AS DOG ,* from billing.account A
INNER JOIN BILLING.BILL B
ON A.ACCOUNTKEY = B.ACCOUNTKEY
 where DOG = 1

the error is "invalid column name 'DOG'
can someone help
jamesmetcalf74Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Are you sure you want RANK(), or maybe needing ROW_NUMBER()

Either way, it needs to be a subquery or a CTE...
;with cte as
(
  select RANK () OVER (PARTITION BY B.ACCOUNTKEY ORDER BY B.ADDDTTM DESC) AS DOG ,*
  from billing.account A
  INNER JOIN BILLING.BILL B ON A.ACCOUNTKEY = B.ACCOUNTKEY
) select * from CTE
  where DOG = 1

Open in new window

Or a subquery
select * from
(
  select RANK () OVER (PARTITION BY B.ACCOUNTKEY ORDER BY B.ADDDTTM DESC) AS DOG ,*
  from billing.account A
  INNER JOIN BILLING.BILL B ON A.ACCOUNTKEY = B.ACCOUNTKEY
) s
where DOG = 1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
Mark's answer is correct.  If you Google search for 'SQL Query Order of Execution' you'll find the below sequential steps for how queries are executed.  WHERE is processed before SELECT, event though in T-SQL the SELECT appears up top, so when query processing hits the WHERE DOG = 1 it doesn't know what DOG is as it has not been processed/defined yet, so it throws the error.

The easy way around this is to throw the main query into a CTE/subquery, and then handle the WHERE in the main query.

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE or ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
jamesmetcalf74Author Commented:
Thanks guys
it totally worked
i am curious
what does the lone 's' mean
Mark WillsTopic AdvisorCommented:
The lone 'S' is the Table Alias for the subquery.

Essentially the subquery is like a table - well - it is  in fact a derived table - and needs a name. So, gave it the name 'S' - but should take the time to make its name (Alias) meaningful
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.