选择带有最新时间戳的记录

Question

我有两个表，从这里选择用户，系统和软。

软记录应该是最新的"tstamp2“

第一:表系统

USER       SYSTEM           ltstamp           
======-----======----===================
User1       LA1      2013-05-06 11:27:26
User2       LA2      2013-06-07 11:27:26 

第二:表格软件

Soft     SYSTEM            tstamp2
=====----=====------===================
  Av1      LA1       2013-04-06 10:27:26
  Av2      LA1       2013-05-06 11:27:26
  Av1      LA2       2013-04-06 10:27:26
  Av2      LA2       2013-06-07 11:27:26

Answer 1

SELECT s.user, s.system, sw.max_tstamp, sw2.soft
FROM
  systems s INNER JOIN (SELECT system, MAX(tstamp2) AS max_tstamp
                        FROM software
                        GROUP BY system) sw
  ON s.system = sw.system INNER JOIN software sw2
  ON s.system = sw2.system AND sw.max_tstamp=sw2.tstamp2

请看小提琴这里。

Answer 2

你需要一个子请求来完成它。例如：

select * from systems 
where ltstamp = (select top 1 ltstamp from systems order by ltstamp desc)

Answer 3

尝尝这个,

select user, system,soft , min(tstamp2) tstamp2 from systems s inner join software w on s.SYSTEM=w.SYSTEM group by user