Help with the query !!!

• Shadab Shah

  SSCarpal Tunnel

  Points: 4759

  More actions

  December 17, 2013 at 10:51 am

  #301472

  Hi all experts,

  Please consider the below query

  create table #temp(Id INT,ItemId NVARCHAR(50),VersionId INT,SubVersionId INT,PurchaseDate DATE,Value INT)

  INSERT INTO #temp VALUES(1,'DELL-Y300',1,21,'2013-09-01',36)

  INSERT INTO #temp VALUES(1,'DELL-Y300',1,22,'2013-09-01',52)

  INSERT INTO #temp VALUES(1,'DELL-Y300',1,23,'2013-09-01',52)

  INSERT INTO #temp VALUES(1,'DELL-Y300',1,24,'2013-09-01',52)

  INSERT INTO #temp VALUES(2,'DELL-Y300',1,21,'2013-10-01',47)

  INSERT INTO #temp VALUES(2,'DELL-Y300',1,22,'2013-10-01',36)

  INSERT INTO #temp VALUES(3,'DELL-Y300',1,21,'2013-09-01',100)

  INSERT INTO #temp VALUES(3,'DELL-Y300',1,22,'2013-09-01',256)

  I am trying to store the result in a temp table, the result should contain the records for 2013-10-01 and the latest record of 2013-09-01 i.e. the result with Id=3.

  I tried using self join and using subquery, but i am not able to get rid of old records.

  The result should look something like this

  DELL-Y300 1 23 2013-09-01 52

  DELL-Y300 1 24 2013-09-01 52

  DELL-Y300 1 21 2013-10-01 47

  DELL-Y300 1 22 2013-10-01 36

  DELL-Y300 1 21 2013-09-01 100

  DELL-Y300 1 22 2013-09-01 256

  The old records for 2013-09-01 are shown in the table because there SubVersionId is different.

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  December 17, 2013 at 11:09 am

  #1674187

  Use GROUP BY.

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]