A Sticky One

• Flopity

  Grasshopper

  Points: 18

  More actions

  December 30, 2003 at 12:33 am

  #101829

  I have a table named Test_Results that contains A_Test_Result which is related to an equipment table by a field Equip_ID, the Equipment table contains details of equipment that needs testing. The test results are many to one Equipment. I need to extract the last A_Test_Result for all of the equipment.

  Thank you for your assistance

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  December 30, 2003 at 1:47 am

  #488132

  - can you define "last" ?

  does your table Test_Results contain a column with datetime of registration ?

  - can you post both tables DDL ?

  Edited by - alzdba on 12/30/2003 01:49:01 AM

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  December 30, 2003 at 2:48 am

  #488146

  create table Test_Results

  (Equip_ID int,

  A_Test_Result varchar(50),

  Test_Date datetime)

  insert into Test_Results values (1, 'test11', getdate())

  insert into Test_Results values (1, 'test12', getdate())

  insert into Test_Results values (2, 'test21', getdate())

  insert into Test_Results values (2, 'test22', getdate())

  create table Equipment (Equip_ID int, Equip_name varchar(50))

  insert into Equipment values (1, 'Equip1')

  insert into Equipment values (2, 'Equip2')

  selectE.Equip_ID,

  E.Equip_name,

  T.A_Test_Result,

  T.Test_Date

  fromEquipment E join Test_Results T on E.Equip_ID = T.Equip_ID

  whereTest_Date = (select max(Test_Date)

  fromTest_Results T1 where T.Equip_ID = T1.Equip_ID)

  Bye

  Gabor

  
  
  Bye
  Gabor

• Flopity

  Grasshopper

  Points: 18

  More actions

  December 30, 2003 at 4:48 pm

  #488313

  quote:

  ---

  create table Test_Results

  (Equip_ID int,

  A_Test_Result varchar(50),

  Test_Date datetime)

  insert into Test_Results values (1, 'test11', getdate())

  insert into Test_Results values (1, 'test12', getdate())

  insert into Test_Results values (2, 'test21', getdate())

  insert into Test_Results values (2, 'test22', getdate())

  create table Equipment (Equip_ID int, Equip_name varchar(50))

  insert into Equipment values (1, 'Equip1')

  insert into Equipment values (2, 'Equip2')

  selectE.Equip_ID,

  E.Equip_name,

  T.A_Test_Result,

  T.Test_Date

  fromEquipment E join Test_Results T on E.Equip_ID = T.Equip_ID

  whereTest_Date = (select max(Test_Date)

  fromTest_Results T1 where T.Equip_ID = T1.Equip_ID)

  Bye

  Gabor

  ---

  That has got it, thank you again.