SQL-запрос для получения последней строки для каждого экземпляра заданного ключа

Я пытаюсь получить ip, user и самую последнюю временную метку из таблицы, которая может содержать как текущий ip для пользователя, так и один или несколько предыдущих ips. Мне нужна одна строка для каждого пользователя, содержащего самый последний ip и связанный с ним временной штамп. Поэтому, если таблица выглядит так:

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? В случае, если это имеет значение, СУБД - Postgresql.

Ответ 1

Попробуйте следующее:

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

Отличное элегантное решение с функцией окна 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

Что-то вроде этого:

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

должен это сделать.

Ответ 4

Оба вышеупомянутых ответа предполагают, что у вас есть только одна строка для каждого пользователя и time_stamp. В зависимости от приложения и детализации вашего time_stamp это может быть недействительным. Если вам нужно иметь дело со связями time_stamp для данного пользователя, вам нужно будет расширить один из ответов, приведенных выше.

Чтобы записать это в один запрос, потребуется другой вложенный подзапрос - все начнет становиться все более грязным, и производительность может пострадать.

Я бы очень хотел добавить это в качестве комментария, но у меня пока нет репутации 50, так жаль, что вы разместили в качестве нового ответа!

Ответ 5

Не могу оставлять комментарии, но ответ @Cristi S работает для меня.

В моем сценарии мне нужно было сохранить только последние 3 записи в Lowest_Offers для всех product_ids.

Нужно переработать свой 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

Я использую это, потому что я возвращаю результаты из другой таблицы. Хотя я пытаюсь избежать вложенного соединения, если он помогает с меньшим шагом. Ну что ж. Он возвращает то же самое.

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