SQL查询以获取给定键的每个实例的最新行 [英] SQL query to get most recent row for each instance of a given key

问题描述

我正在尝试从一个表中获取ip，用户和最新时间​​戳，该表可能包含用户的当前ip和一个或多个先前的ip.我想为每个包含最新ip和相关时间戳的用户排一行.因此，如果表看起来像这样:

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this:

username      |  ip      |  time_stamp  
--------------|----------|--------------  
ted           | 1.2.3.4  | 10  
jerry         | 5.6.6.7  | 12  
ted           | 8.8.8.8  | 30  

我希望查询的输出为:

jerry    |  5.6.6.7   |  12
ted      |  8.8.8.8   |  30  

我可以在单个sql查询中执行此操作吗?如果需要的话，DBMS是Postgresql.

Can I do this in a single sql query? In case it matters, the DBMS is Postgresql.

推荐答案

尝试一下:

Select u.[username]
      ,u.[ip]
      ,q.[time_stamp]
From [users] As u
Inner Join (
    Select [username]
          ,max(time_stamp) as [time_stamp]
    From [users]
    Group By [username]) As [q]
On u.username = q.username
And u.time_stamp = q.time_stamp

这篇关于SQL查询以获取给定键的每个实例的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！