latest value entered based on last modified date

• Adelphi

  SSC Enthusiast

  Points: 111

  More actions

  July 23, 2012 at 5:06 am

  #259293

  Hi everyone,

  I have a table that holds the following data:

  ProjectCustomerUpdated ByAmount TrailUpdated On

  Project 1Customer AUser 1£1,142.0005/01/2012 10:27

  Project 1Customer AUser 1£1,839.0005/01/2012 10:59

  Project 2Customer BUser 2£121,995.0024/05/2012 09:26

  Project 3Customer AUser 2£101,662.0026/06/2012 10:01

  Project 4Customer DUser 1£18,191.0014/05/2012 13:40

  Project 4Customer DUser 1£46,906.0018/05/2012 13:09

  Project 4Customer DUser 2£58,309.0018/06/2012 13:39

  Project 4Customer DUser 2£76,500.0019/06/2012 15:44

  Project 4Customer DUser 1£88,112.0003/07/2012 14:14

  I want only one record per project and per customer based on when each record was last modified (Updated On) regardless of user (updated by), so the I want the results to be:

  PProjectCustomerUpdated ByAmount TrailUpdated On

  Project 1Customer AUser 1£1,839.0005/01/2012 10:59

  Project 2Customer BUser 2£121,995.0024/05/2012 09:26

  Project 3Customer AUser 2£101,662.0026/06/2012 10:01

  Project 4Customer DUser 1£88,112.0003/07/2012 14:14

  Many thanks in advance for your help

• Jeff Moden

  SSC Guru

  Points: 1004627

  More actions

  July 23, 2012 at 5:34 pm

  #1516524

  Adelphi (7/23/2012)

  ---

  Hi everyone,

  I have a table that holds the following data:

  ProjectCustomerUpdated ByAmount TrailUpdated On

  Project 1Customer AUser 1£1,142.0005/01/2012 10:27

  Project 1Customer AUser 1£1,839.0005/01/2012 10:59

  Project 2Customer BUser 2£121,995.0024/05/2012 09:26

  Project 3Customer AUser 2£101,662.0026/06/2012 10:01

  Project 4Customer DUser 1£18,191.0014/05/2012 13:40

  Project 4Customer DUser 1£46,906.0018/05/2012 13:09

  Project 4Customer DUser 2£58,309.0018/06/2012 13:39

  Project 4Customer DUser 2£76,500.0019/06/2012 15:44

  Project 4Customer DUser 1£88,112.0003/07/2012 14:14

  I want only one record per project and per customer based on when each record was last modified (Updated On) regardless of user (updated by), so the I want the results to be:

  PProjectCustomerUpdated ByAmount TrailUpdated On

  Project 1Customer AUser 1£1,839.0005/01/2012 10:59

  Project 2Customer BUser 2£121,995.0024/05/2012 09:26

  Project 3Customer AUser 2£101,662.0026/06/2012 10:01

  Project 4Customer DUser 1£88,112.0003/07/2012 14:14

  Many thanks in advance for your help

  You'll get much more timely and better help if you read and heed the article at the first link in my signature line below.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  July 23, 2012 at 5:43 pm

  #1516527

  try the rownumber function with partition by

  Jayanth Kurup[/url]