limiting record set 1

[jlsmithhartfiel]

Thanks to this forum, I've managed to get the last log(s) records based on max(date) grouped by uid. Results are below. The only (!) problem I have now is you'll notice the first two rows are for the same uid, and they also have the same date, BUT I need to have only 1 final result for each uid. So in this case, I need the newvalue that is not the old value for the other record. Here the final record desired for uid 1022736 is:

1022736  2002-01-03 00:00:00.000 act        tnafs     

uid      tstamp                  oldvalue   newvalue  
-------- ----------------------- ---------- ----------
1022736  2002-01-03 00:00:00.000 act        tnafs     
1022736  2002-01-03 00:00:00.000 exp        act       
1077779  2002-01-01 00:00:00.000 TNAFS      exp       
1154970  2002-02-01 00:00:00.000 ACT        exp       
1220658  2002-01-16 00:00:00.000 PEND       closd     
1220663  2002-02-12 00:00:00.000 PEND       closd     
1228364  2002-02-27 00:00:00.000 act        exp       
1273211  2002-01-04 00:00:00.000 ACT        pend      
1273211  2002-01-04 00:00:00.000 pend       closd     


declare @fd datetime, @td datetime

set @fd = '1/1/02'
set @td = '3/1/02'

select l.uid, l.tstamp, l.oldvalue, l.newvalue
from propertylogs l
where l.tablename = 'prp'
  and l.fieldname = 'liststatus'
  and l.tstamp <= @td
  and l.tstamp =
   (select max(t.tstamp) 
     from propertylogs t 
	where t.uid = l.uid)
order by l.uid, l.tstamp

Any suggestions would be greatly appreciated!

Thanks in advance, Jessica

 

[tlbroadbent]

How about trying something like this?

select l.uid, l.tstamp, l.oldvalue, l.newvalue
from propertylogs l
where l.tablename = 'prp'
and l.fieldname = 'liststatus'
and l.tstamp <= @td
and l.tstamp =
(select max(tstamp)
from propertylogs
where t.uid = l.uid)
and not exists
(select *
from propertylogs
where uid = l.uid
and tstamp = l.tstsmp
and oldvalue = l.newvalue)
order by l.uid, l.tstamp
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[alfredp77]

i have a simpler query:

select t1.uid,t1.tstamp,t1.oldvalue,t1.newvalue
from propertylogs t1 left join propertylogs t2
on t1.uid=t2.uid and t1.newvalue=t2.oldvalue
where t2.uid is null

by the way, can you have a circular values in your table like this:

uid tstamp oldvalue newvalue
-------- ----------------------- ---------- ----------
1022736 2002-01-03 00:00:00.000 act tnafs
1022736 2002-01-03 00:00:00.000 exp act
1022736 2002-01-03 00:00:00.000 tnafs exp

in this case my query won't display anything for uid=1022736.

 

[jlsmithhartfiel]

Thanks so much! I ended up using Terry's code.

I am new to SQL, so I sometimes have trouble translating the language I know into the correct syntax.

I'm glad these forums are here! Jessica