Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Posts
    2

    Unanswered: Query for getting latest n items

    Hi,

    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.

    e.g.

    (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, 168.0.0.1, 168.0.0.2, 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.

  2. #2
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    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.
    Code:
    SELECT *
    FROM (
      SELECT id, name, lat, lng, sIP, dIP, timestamp,
        row_number() OVER (PARTITION BY name ORDER BY timestamp DESC) AS rn
      FROM src_table
    ) sub
    WHERE rn <= 9
    ORDER BY name, timestamp DESC

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •