To get the number of revisions against each PartID

[zsyed]

Can anyone suggest what's wrong with this SQL code?

Select imrpartid, imrpartrevisionid from partrevisions inner join
( Select imrpartid, imrpartrevisionid, count (*) as cnt from partrevisions where imreffectiveenddate is null group by partrevisions.imrpartid,partrevisions.imrpartrevisionid) as ptr
on partrevisions.imrpartid = ptr.imrpartid and partrevisions.imrpartrevisionid = ptr.imrpartrevisionid where cnt >1

 

[gmmastros]

I don't see anything obvious. What error message are you getting?

By the way, it looks like this can be simplified to this...

Select imrpartid, imrpartrevisionid
from   partrevisions 
where  imreffectiveenddate is null 
group by partrevisions.imrpartid,
       partrevisions.imrpartrevisionid
having Count(*) > 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[zsyed]

Hi George,

Thanks for the response. I was getting Ambigious column name imrpartid.
But I'm able to get the result as per your suggested query but not all the detail that I wanted. I can only get the number of active revisions by partid.

Select imrpartid, count(*) as cnt
from partrevisions
where imreffectiveenddate is null
group by partrevisions.imrpartid having count(*) >1

If I want to print imrpartrevisionid, it would not let me print along with this query. Here is what I would like to print. I tried both with and wihtout revision id.

Select imrpartid, imrpartrevisionid, count(*) as cnt
from partrevisions
where imreffectiveenddate is null
group by partrevisions.imrpartid, imrpartrevisionid having count(*) >1

 

[zsyed]

I figured out this query as I wanted. Here is the query.

Select * from partrevisions where imrpartid in (Select imrpartid
from partrevisions
where imreffectiveenddate is null
group by partrevisions.imrpartid having count(*) >1)

Thanks to George for idea.
Thanks and Regards

 

[SQLBill]

That's a solution...but here is why you got the error you did. In your version, you have two tables - partrevisions and ptr. ptr is made of of values taken from partrevisions including the imrpartid column. This column is in partrevisions AND one of the columns selected to make up ptr.

The problem is that in your main SELECT you don't say which of the two imrpartid columns to use. This would have worked:

Select pr.imrpartid, pr.imrpartrevisionid  --these needed aliased
   from partrevisions pr   --I aliased this for easier reading
    inner join
     ( Select imrpartid, imrpartrevisionid, count (*) as cnt 
       from partrevisions 
       where imreffectiveenddate is null 
       group by imrpartid, imrpartrevisionid) as ptr  --you don't need to identify the table here as there is only one table used
     on pr.imrpartid = ptr.imrpartid and pr.imrpartrevisionid = ptr.imrpartrevisionid where cnt >1

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875