SQL查询，获取给定键的每个实例的最新行

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:

我正在尝试从一个表中获取ip、用户和最近的时间戳，这个表可能包含一个用户的当前ip和一个或多个以前的ip。我希望每个用户有一行包含最新的ip和相关的时间戳。如果一个表格是这样的

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

I'd expect the output of the query to be:

我希望查询的输出是:

jerry    |  5.6.6.7   |  12
ted      |  8.8.8.8   |  30

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

我可以在一个sql查询中实现这一点吗?如果重要的话，DBMS是Postgresql。

6 个解决方案

#1

Try this:

试试这个:

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

#2

Nice elegant solution with ROW_NUMBER window function (supported by PostgreSQL - see in SQL Fiddle):

使用ROW_NUMBER窗口函数的漂亮优雅解决方案(由PostgreSQL支持，参见SQL Fiddle):

SELECT username, ip, time_stamp FROM (
 SELECT username, ip, time_stamp, 
  ROW_NUMBER() OVER (PARTITION BY username ORDER BY time_stamp DESC) rn
 FROM Users
) tmp WHERE rn = 1;

#3

Something like this:

是这样的:

select * 
from User U1
where time_stamp = (
  select max(time_stamp) 
  from User 
  where username = U1.username)

should do it.

应该这样做。

#4

Both of the above answers assume that you only have one row for each user and time_stamp. Depending on the application and the granularity of your time_stamp this may not be a valid assumption. If you need to deal with ties of time_stamp for a given user, you'd need to extend one of the answers given above.

以上两个答案都假设每个用户只有一行，并且时间戳。根据应用程序和时间戳的粒度，这可能不是一个有效的假设。如果需要处理给定用户的time_stamp关系，则需要扩展上面给出的其中一个答案。

To write this in one query would require another nested sub-query - things will start getting more messy and performance may suffer.

要在一个查询中编写这个，将需要另一个嵌套子查询—事情将变得更加混乱，性能可能会受到影响。

I would have loved to have added this as a comment but I don't yet have 50 reputation so sorry for posting as a new answer!

我很想把这句话加在评论里，但是我还没有50个声誉，所以很抱歉我把它作为一个新的回复。

#5

Can't post comments yet, but @Cristi S's answer works a treat for me.

不能发表评论,但@Cristi S回答我的治疗工作。

In my scenario, I needed to keep only the most recent 3 records in Lowest_Offers for all product_ids.

在我的场景中，我只需要在Lowest_Offers中保存所有product_id的最近3条记录。

Need to rework his SQL to delete - thought that this would be ok, but syntax is wrong.

需要重新修改他的SQL来删除——认为这是可以的，但是语法是错误的。

DELETE from (
SELECT product_id, id, date_checked,
  ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY date_checked DESC) rn
FROM lowest_offers
) tmp WHERE > 3;

#6

I've been using this because I'm returning results from another table. Though I'm trying to avoid the nested join if it helps w/ one less step. Oh well. It returns the same thing.

我使用这个是因为我要返回另一个表的结果。虽然我试图避免嵌套连接，如果它有助于减少一步。哦。它返回相同的东西。

select
users.userid
, lastIP.IP
, lastIP.maxdate

from users

inner join (
    select userid, IP, datetime
    from IPAddresses
    inner join (
        select userid, max(datetime) as maxdate
        from IPAddresses
        group by userid
        ) maxIP on IPAddresses.datetime = maxIP.maxdate and IPAddresses.userid = maxIP.userid
    ) as lastIP on users.userid = lastIP.userid

#1