postgres SQL query to find the recent entry for particular duplicate value-CodePudding

I'm having table with two columns namely hostname and timestamp. Host name will be having many duplicates values as mentioned below. I need to get the timestamp of the last entry of particular duplicated value .In this case,There are duplicate values for both Server AB and CD.but i need to find the duplicate value for Hostname=AB. IN real time this table will be containing many duplicates for hostname.

|hostname |timestamp|
|:........|---------:|
|AB       |14Aug2021 11:12|
|BC       |15Aug2021 11:12|
|CD       |16Aug2021 11:12|
|CD      |22Aug2021 11:12|
|AB      |25Aug2021 11:12|
|CD      |26Aug2021 11:12|

In the above table I need to get the latest entry of time stamp for hostname=AB

CodePudding user response：

You could try a LIMIT query here:

SELECT hostname, timestamp
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.hostname = t1.hostname AND
                    t2.timestamp <> t1.timestamp)
ORDER BY timestamp DESC
LIMIT 1;

CodePudding user response：

That should be as simple as

SELECT max(timestamp) FROM tab WHERE hostname = 'AB';

The aggregate max will return the maximal timestamp from all rows matching the WHERE condition.

CodePudding user response：

distinct on is what you need. Please note the order by clause.

select distinct on (hostname) *
from the_table 
where hostname = 'AB'
order by hostname, "timestamp" desc;

This particular illustration returns the latest record for hostname 'AB'. You can use other conditions as you wish or no where at all which will produce the latest records for all hostnames. If you need just the timestamp for one hostname only then the answer of @LaurenzAlbe should be better.