Self Join

• Chris Lane-152984

  Mr or Mrs. 500

  Points: 576

  More actions

  July 19, 2006 at 4:52 pm

  #113698

  Hi,

  I need help with a Self Join, I have a table with productID, groupID and version number all making up a composite primary key all of type int.

  I need to be able to query the table for latest version number in order to get the most current description of the product.

  I tried modifying the example found here http://qa.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=3&faqid=364   but that thing ran forever.

   

  Thanks in advance for any help you might provide.

• Sergiy

  SSC Guru

  Points: 110192

  More actions

  July 19, 2006 at 5:31 pm

  #650273

  SELECT T1.*

  FROM <Table> T1

  INNER JOIN (select productID, groupID, MAX(version) -- whatever method you use to identify the latest version

  from <Table>

  group by productID, groupID) T2 ON T1.productID = T2.productID AND T1.groupID = T2.groupID AND T1.version = T2.version

  _____________
  Code for TallyGenerator

• Chris Lane-152984

  Mr or Mrs. 500

  Points: 576

  More actions

  July 20, 2006 at 9:31 pm

  #650586

  Thank You!