Hi there, I am trying to trim down this query to something a little more readable.
The query itself creates a view from a real table called ‘network’, only returning the latest value for each unique nic_name for a particular key (latest being defined by the datetimestamp value)
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `network_view` AS select
`t2`.`key` AS `key`,
`t2`.`nic_name` AS `nic_name`,
`t2`.`nic_vp` AS `nic_vp`,
`t2`.`nic_ipmp` AS `nic_ipmp`,
`t2`.`nic_ip` AS `nic_ip`,
`t2`.`nic_mac` AS `nic_mac`,
`t2`.`nic_mask` AS `nic_mask`,
`t2`.`nic_bcast` AS `nic_bcast`,
`t2`.`nic_zone` AS `nic_zone`,
`t2`.`nic_link` AS `nic_link`,
`t2`.`nic_duplex` AS `nic_duplex`,
`t2`.`nic_speed` AS `nic_speed`,
`t2`.`nic_switch` AS `nic_switch`,
`t2`.`nic_port` AS `nic_port`,
`t2`.`nic_vlan` AS `nic_vlan`,
`t2`.`nic_status` AS `nic_status`,
`t2`.`datetimestamp` AS `datetimestamp` from `network` `t2` where ((`t2`.`key`,`t2`.`nic_name`,`t2`.`datetimestamp`) in (select
`network`.`key` AS `key`,
`network`.`nic_name` AS `nic_name`,max(`network`.`datetimestamp`) AS `MAX(datetimestamp)`
from `network` where ((`network`.`key` = `t2`.`key`) and (`network`.`nic_name` = `t2`.`nic_name`))
group by `network`.`key`,`network`.`nic_name`) and (`t2`.`nic_status` <> 'removed'))
order by `t2`.`key`,`t2`.`nic_name`
I have tried this but its doesnt seem to be working
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `original` AS select t2.key
, t2.nic_name
, t2.nic_vp
, t2.nic_ipmp
, t2.nic_ip
, t2.nic_mac
, t2.nic_mask
, t2.nic_bcast
, t2.nic_zone
, t2.nic_link
, t2.nic_duplex
, t2.nic_speed
, t2.nic_switch
, t2.nic_port
, t2.nic_vlan
, t2.nic_status
, t2.datetimestamp FROM network t2
WHERE ((t2.key,t2.nic_name,t2.datetimestamp) IN (SELECT network.key
, network.nic_name
, MAX(network.datetimestamp)
FROM network WHERE ((network.key = t2.key)
AND (network.nic_name = t2.nic_name))
GROUP BY network.key,network.nic_name)
AND(t2.nic_status <> 'removed'))
ORDER BY t2.key,t2.nic_name;
Is there something I am missing when trying to simplify this query ?
any help would be greatly appreciated