Get latest record without timestamp

• anthony wang

  Old Hand

  Points: 323

  More actions

  January 11, 2007 at 9:30 am

  #153380

  Hi, I have a table including 2 fields "AccountNo" and "AdmissionReason". These two fields are one primary key. Now my question is how can I get the latest record for each accountno without using any datetime field. For example.

       Accountno             AdmissionReason

  1     100001                 fever

  2     100001                 cold

  3     100002                 fever

  I want to get record nuber 2 and number 3.

  Thank you so much for your help.

   

   

   

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 11, 2007 at 9:54 am

  #682725

  without more information, I don't believe you can.

  is there an identity column in the tabe? is there a datetime field for creation or updated?

  if the primary key might be (Accountno,AdmissionReason), then the clustered index is probably going to store the data as Accountno ASC,AdmissionReason ASC for speed of search purposes, so if I were to add a record of 100001,'Asthma', it would get auto sorted to be the "first record"for 100001, before "cold" and "fever"

  here's my point:

  create table #test (

  Accountno int,

  AdmissionReason varchar(30),

  CONSTRAINT [PK__ACCTNO_ADMIN] PRIMARY KEY (Accountno,AdmissionReason)

  )

  insert into #test (Accountno,AdmissionReason) values(100001,'fever')

  insert into #test (Accountno,AdmissionReason) values(100001,'cold')

  insert into #test (Accountno,AdmissionReason) values(100002,'fever')

  insert into #test (Accountno,AdmissionReason) values(100001,'asthma')

  select * from #test

  results:

  Accountno	AdmissionReason
  100001	asthma
  100001	cold
  100001	fever
  100002	fever

   

  note that the results ARE NOT IN THE ORDER OF INSERTION!!!

  if you have a datefiled in there, then we could group by the accountno and find the max date to give the results you are looking for, so we'd need more info

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Johan Bijnens

  SSC Guru

  Points: 135369

  More actions

  January 11, 2007 at 2:58 pm

  #682787

  actualy it is a common pitfall to determine "age"/"most recent",.. based on an autonumber !

  Add a datetime-column with default getdate() or getUTCdate().

  I takes 8 bytes a row, but at least you get the advantage to know.

  We add a datetime_inserted column with default getdate() to _all_ our tables !

  If you have a problem with not knowing this datetime for the existing rows, determine a common known startdate and update the current rows with it or perform e.g.

  alter table yourtable add datetimeInserted datetime not null default '2006-04-01';

  after the successfull execution, drop the default, and alter it to GETDATE() or GETUTCDATE()

  .

  Document your choice !  

  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