Select multiple columns based on the max of another column

Question

I have and Oracle 11g Database with the following three tables (simplified):

IP Table, containing an IP identifier, the IP, and IP status and a FQDN. IPs might be repeated.

+-------+-------------+-----------+-----------+ | ID_IP | IP | IP_STATUS | FQDN | +-------+-------------+-----------+-----------+ | 1 | 192.168.1.1 | 1 | test.com | | 2 | 192.168.1.1 | 2 | test.com | | 3 | 192.168.1.1 | 3 | test.com | | 4 | 10.10.45.12 | 2 | test2.com | +-------+-------------+-----------+-----------+

VLAN Table, containing and VLAN identifier and the VLAN number

+---------+-------------+ | VLAN_ID | VLAN_NUMBER | +---------+-------------+ | 1 | 3 | | 2 | 5 | | 3 | 7 | +---------+-------------+

A Table correlating VLANs and IPs:

+-------+---------+ | IP_ID | VLAN_ID | +-------+---------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 2 | +-------+---------+

In the actual IP Table, the primary key is the tuple (IP, IP_STATUS). My goal is to create a new table eliminating the IP_STATUS, and to do that, I want to aggregate IPs and get the ID_IP and FQDN of the IP whose VLAN_NUMBER is higher. The answer for the SELECT query would be something like this:

+-------+-------------+-----------+ | ID_IP | IP | FQDN | +-------+-------------+-----------+ | 3 | 192.168.1.1 | test.com | | 4 | 10.10.45.12 | test2.com | +-------+-------------+-----------+

I can get the IP using the following query:

SELECT i.IP, max(v.VLAN_ID) FROM IPS i LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA GROUP BY i.IP;

What I don't know is how to get the other columns. I tried with a subquery like the following:

SELECT i.ID_IP, i.IP, i.FQDN FROM IPS i WHERE i.IP IN ( SELECT i.IP, max(v.VLAN_ID) FROM IPS i LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA GROUP BY i.IP; )

But it doesn't work, since the subquery returns two values, and I need the max(vlan.VLAN_ID) to do the aggregation.

How could I get the right IP_ID?

Thank you!

Philippe Banwarth · Accepted Answer · 2016-04-19 10:36:32Z

You can use an analytical clause to split by IP and order by VLAN_NUMBER, then filter to retain only the first line in each group :

SELECT ID_IP, IP, FQDN FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY i.IP ORDER BY v.VLAN_NUMBER DESC) AS NB, i.ID_IP, i.IP, i.FQDN FROM IPS i LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP = v_i.ID_IP LEFT JOIN VLANS v ON v_i.VLAN_ID = v.VLAN_ID ) t_a WHERE NB = 1

Ramana · Accepted Answer · 2016-04-19 10:35:34Z

You may want try WITH clause. Roughly...

WITH IPWITHMAXVLANID(IP, MAXVLAN) AS ( SELECT i.IP, max(v.VLAN_ID) FROM IPS i LEFT JOIN VLAN_IP_REL v_i ON i.ID_IP=v_i.ID_IP LEFT JOIN VLANS v ON v_i.ID_VLAN=v.ID_INSTANCIA GROUP BY i.IP ) SELECT i.ID_IP, i.IP, i.FQDN, iml.MAXVLAN FROM IPS i INNER JOIN IPWITHMAXVLANID iml on i.IP = imp.IP

Hope this helps.

Collectives™ on Stack Overflow

Select multiple columns based on the max of another column

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related