var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Query for getting latest n items
I have a table of the following structure:
data(id, name, lng, lat, sIP, dIP, timestamp)
Basically it stores different nodes and their geographical data (longitude, latitude) and source/destination IP addresses including the timestamp when the data was recorded.
I would like to write a query that returns me the name, lng, lat, sIP, dIP, timestamp of the latest n entries for each of the nodes (node identified by the "name" column).
Basically, a node can have multiple entries with different sIP, dIP and timestamp entries.
(1, Node1, -0.1, 23, 10.0.0.1, 10.0.0.2, 123345685)
(2, Node1, -0.1, 23, 192.168.0.1, 192.168.0.2, 12340987)
(3, Node2, 45, 1.2, 22.214.171.124, 126.96.36.199, 1234346868)
So I want to get the latest "n" entries for each node.
I am not sure on how to formulate the SQL query.
Any help is much appreciated.
Thanks in advance.
Assuming you are on 8.4 you can do this with analytic functions. Unfortunately you can't use them in a where clause so you need a subquery.
SELECT id, name, lat, lng, sIP, dIP, timestamp,
row_number() OVER (PARTITION BY name ORDER BY timestamp DESC) AS rn
WHERE rn <= 9
ORDER BY name, timestamp DESC