registros - numero de fila sql server

Consulta SQL para obtener la fila más reciente para cada instancia de una clave determinada

Question

Estoy tratando de obtener la ip, el usuario y la marca de tiempo más reciente de una tabla que puede contener la ip actual para un usuario y uno o más ips anteriores. Me gustaría una fila para cada usuario que contenga la IP más reciente y la marca de tiempo asociada. Entonces, si una tabla se ve así:

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

Esperaría que el resultado de la consulta sea:

jerry | 5.6.6.7 | 12
ted | 8.8.8.8 | 30

¿Puedo hacer esto en una única consulta SQL? En caso de que importe, el DBMS es Postgresql.

Answer 1

Algo como esto:

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

Deberías hacerlo.

Answer 2

Buena solución elegante con función de ventana ROW_NUMBER (compatible con PostgreSQL - ver en 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;

Answer 3

He estado usando esto porque estoy devolviendo resultados de otra tabla. Aunque estoy tratando de evitar la unión anidada si ayuda con un paso menos. Oh bien. Devuelve lo mismo.

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

Answer 4

Las dos respuestas anteriores asumen que solo tiene una fila para cada usuario y marca de tiempo. Dependiendo de la aplicación y la granularidad de su time_stamp, esta puede no ser una suposición válida. Si necesita lidiar con lazos de time_stamp para un usuario determinado, deberá extender una de las respuestas dadas anteriormente.

Escribir esto en una consulta requeriría otra subconsulta anidada: las cosas comenzarán a ser más complicadas y el rendimiento puede verse afectado.

Me hubiera encantado haber agregado esto como comentario, pero aún no tengo 50 reputación, ¡lo siento mucho por publicar como una nueva respuesta!

Answer 5

No puedo publicar comentarios todavía, pero la respuesta de @Cristi S funciona para mí.

En mi caso, necesitaba mantener solo los 3 registros más recientes en Lowest_Offers para todos los product_ids.

Necesita volver a trabajar su SQL para eliminar, pensó que esto estaría bien, pero la sintaxis es incorrecta.

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;

Answer 6

Prueba esto:

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