Good morning,
I'm new to subqueries, I have a table that registers ip addresses of computers connected to the day, my idea is to list only the first two or a computer corresponding to an ip address I explain (table equipment)
my current query, of course shows everything:
SELECT id_host, hostname, dir_ip, fecha_reg where DATE(fecha_reg) = CURDATE()
Please can you help me.
What version of MySQL are you using?
In the next version of MySQL (8.0) you can use Window Functions ( 12.19 Window Functions , here an example, in MariaDB, where the functionality is already implemented ( Window Functions ):
SELECT
'der'.'id_host',
'der'.'hostname',
'der'.'dir_ip',
'der'.'fecha_reg',
'dir_ip_rank'
FROM (
SELECT
'id_host',
'hostname',
'dir_ip',
'fecha_reg',
ROW_NUMBER() OVER(PARTITION BY 'dir_ip' ORDER BY 'id_host') AS 'dir_ip_rank'
FROM 'equipos'
) 'der'
WHERE 'der'.'dir_ip_rank' <= 2;
See dbfiddle .
While in MySQL they implement the functionality of Window Functions, one option is to use a query like the following:
SELECT
'der'.'id_host',
'der'.'hostname',
'der'.'dir_ip',
'der'.'fecha_reg',
'der'.'dir_ip_rank'
FROM (
SELECT
'id_host',
'hostname',
'dir_ip',
'fecha_reg',
@'dir_ip_rank' := IF(@'dir_ip_current' = 'dir_ip', @'dir_ip_rank' + 1, 1) 'dir_ip_rank',
@'dir_ip_current' := 'dir_ip'
FROM 'equipos',
(SELECT
@'dir_ip_rank' := 0,
@'dir_ip_current' := ''
) 'der'
ORDER BY 'dir_ip', 'id_host', 'dir_ip_rank'
) 'der'
WHERE 'der'.'dir_ip_rank' <= 2;
See db-fiddle .
Adjust the queries as needed.
NOTE: I'm not sure how you store the IP addresses, in my example, I used VARCHAR (15) for the purpose of the example, however, it is advisable to use the functions INET_ATON () and INET_NTOA () .
I think you can use limit = 2 to limit the number of records that the query returns.
SELECT id_host, hostname, dir_ip, fecha_reg where DATE(fecha_reg) = CURDATE() limit 2
Here you will also find out about the different uses you can give to limit
The second link explains it better
I hope it serves you =)