Link to home
Start Free TrialLog in
Avatar of Jim Metcalf
Jim MetcalfFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Metcalf

ASKER

Thanks guys
it totally worked
i am curious
what does the lone 's' mean
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