CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
matta118
matta118

Reputation: 17

Postgres query find UNique Latest entry

I have a Postgres Table named ip_storage_table and it is storing three values ip_address , virtual_id and a time_stamp

The actual relation between this is initially all I/Ps are having a virtual_id associated with it for eg:

192.168.1.1 has 1000 at 2015-07-20 15:00:00
192.168.1.2 has 2000 at 2015-07-20 15:00:00
192.168.1.3 has 3000 at 2015-07-20 15:00:00

This table contains the virtual_id of each I/P address at a particular time stamp so it can have more entries like

192.168.1.1 has 3000 at 2015-07-20 16:00:00
192.168.1.2 has 1000 at 2015-07-20 16:00:00
192.168.1.3 has 2000 at 2015-07-20 16:00:00
192.168.1.2 has 4000 at 2015-07-20 17:00:00
192.168.1.1 has 2000 at 2015-07-20 18:00:00

I want to run a query such that:

  1. The virtual_id comes only once in the output
  2. The i/p associated with the virtual_id should come with it
  3. and the virtual_id should be latest i.e. in above example 2000 will be with 192.168.1.1 and at 18:00:00 timestamp and all its previous values wont be displayed
  4. If two virtual id's have the same i/p , only the latest one appears in the output

I could have uploaded an image of the database, but I am using Linux and unable to find image editing tools, if you can't get a view of database with above description let me know, I will try again

Upvotes: 0

Views: 64

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If I understand correctly, you can use distinct on:

select distinct on (virtual_id) t.*
from mytable t
order by virtual_id, time_stamp desc;

This will return each virtual_id with its more recent row.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You can simply try:-

SELECT i/p, MIN(virtual_id), MAX(timestamp)
FROM YOUR_TABLE
GROUP BY i/p;

Hope this helps.

See the fiddle. http://sqlfiddle.com/#!15/d7905/5

Upvotes: 0

Related Questions