0

I have a database table where there is a column hostname and few other columns. in hostname, there are many instances of rows with same hostnames.

Ex. 192.0.0.1 has 40 entries and 192.0.0.2 has 35 entries and so on.

Is it possible to get the list of latest entries for each hostname? meaning in the result i should get one latest for 192.0.0.1 and one latest for 192.0.0.2 and so on.

I tried with

SELECT host,cluster,region,service 
  FROM system
 WHERE host IN ("171.33.64.158","171.33.64.159")  
 ORDER BY id DESC LIMIT 1;

but the output contains only one row which is probably the first row of the entire result.

Can anyone please help me in this requirement?

Thanks, Swapnil

MT0
  • 86,097
  • 7
  • 42
  • 90
Swapnil Hadge
  • 5
  • 7
  • 2
    How do you define latest? Do you have a timestamp column or something similar? – Mureinik Nov 28 '20 at 18:39
  • Is your DBMS Oracle as tagged or MySQL as it suggests from the format of the query? – Barbaros Özhan Nov 28 '20 at 18:46
  • `LIMIT 1` will only get one row. – Paul T. Nov 28 '20 at 18:54
  • @PaulT. Oracle does not support the `LIMIT` keyword. – MT0 Nov 28 '20 at 19:38
  • @Mureinik the latest for me would be the last entry for the particular hostname in the table. My database is MySQL – Swapnil Hadge Nov 28 '20 at 20:07
  • "last entry for the particular hostname" It is a fundamental of relational databases that rows are 'unordered'. They are like balls in a basket. There is no concept of 'last' until you SELECT with an ORDER BY clause. Without an ORDER BY there will be, of course, a 'last' row listed. But which specific row it is will be totally random. – EdStevens Nov 28 '20 at 20:46
  • @MT0 ... Thanks for that info, I was only going by the presented query. – Paul T. Nov 28 '20 at 22:26

2 Answers2

0

Try this:

select host,cluster,region,service
from (
   SELECT host,cluster,region,service, row_number() over (partition by host order by id 
      desc) as r_num
   FROM system
   WHERE host IN ("171.33.64.158","171.33.64.159"))
where r_num = 1;

Thanks

gogocho
  • 188
  • 5
-1

For proper ordering you need to convert the IP address into decimal equivalent. I would suggest a function like this:

FUNCTION IP2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
    DecimalIp NUMBER; 
BEGIN

   SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
   INTO DecimalIp
   FROM dual 
   CONNECT BY LEVEL <= 4;
   RETURN DecimalIp;    

END IP2Decimal;

Then your query could be this:

SELECT hostname,
  FIRST_VALUE(host) OVER (ORDER BY IP2Decimal(host)),
  FIRST_VALUE(cluster) OVER (ORDER BY IP2Decimal(host)),
  FIRST_VALUE(region) OVER (ORDER BY IP2Decimal(host)),
  FIRST_VALUE(service) OVER (ORDER BY IP2Decimal(host))
FROM system
GROUP BY hostname;
Wernfried Domscheit
  • 38,841
  • 5
  • 50
  • 81
  • @AlmirCampos, indeed the given query in the question does not correspond with the text. I tried to give a solution that matches the **question** rather than the (not working) query. – Wernfried Domscheit Dec 01 '20 at 06:40