How to get the records which is not equal to 1 in my query

How to ch开发者_StackOverflow社区eck the duplicated records in sql query.

---

There is always going to be a row_number() which is 1, right?

I think you need to nest this query like:

 select * from (YOUR_BIG_QUERY) where Ordinal <> 1

---

as long as where is executed before select, selected aliases are not known in where clause. you can only use the aliases in order by clause.

you have to write like this:

SELECT * FROM 
(
Select TD.ProductAccumRule_Id,TD.PRODUCT_ID,TD.VARIABLE_ID,TD.Accum_code,Ordinal = row_number() over( partition by TD.PRODUCT_ID,TD.VARIABLE_ID,TD.Accum_code order by TD.PRODUCT_ID,TD.VARIABLE_ID,TD.Accum_code) From testdata TD
Join (
select PRODUCT_ID,VARIABLE_ID,Accum_code from testdata where 
isActive = 1
GROUP BY PRODUCT_ID,VARIABLE_ID,Accum_code
having count(*) > 1
) TEMP on TD.Product_Id = temp.Product_Id and TD.Variable_Id = TEMP.Variable_Id and TD.Accum_code = TEMP.Accum_code
where TD.isActive = 1
) myInnerQuery
where Ordinal  <> 1