CODEWITHSUNDEEP
mysqlsql
Venan24
Venan24

Reputation: 23

SQL join: selecting last records from duplicates

I am working on python project "Linux Server Monitor". When you load monitoring website you create your servers and table look like this:

Servers:

+-----------+-----------+---------+-------------+
| server_id | auth_code | user_id | server_name |
+-----------+-----------+---------+-------------+
|         0 | xyz123    |       1 | My Server 1 |
|         1 | aabb      |      45 | BlaBlaUser  |
|         2 | asdfh1    |       1 | My Server 5 |
+-----------+-----------+---------+-------------+

After the server is created, you start python script and it starts to upload monitoring data to this table (where all data is stored, from all users):

Monitoring Data:

+----+-----------+---------+---------+------------+-----------------+------------------+
| id | auth_code | user_id | os_name | os_version |   external_ip   | some_other_table |
+----+-----------+---------+---------+------------+-----------------+------------------+
|  0 | aabb      |      45 | Ubuntu  |      16.04 |    77.88.99.100 | dynamic data     |
|  1 | aabb      |      45 | Ubuntu  |      16.04 |    77.88.99.100 | dynamic data 2   |
|  2 | aabb      |      45 | Ubuntu  |      16.04 |     10.10.10.10 | dynamic data 3   |
|  3 | xyz123    |       1 | Fedora  |         27 |     92.94.95.99 | different data 1 |
|  4 | xyz123    |       1 | Fedora  |         27 |     92.94.95.99 | different data 2 |
|  5 | xyz123    |       1 | Fedora  |         27 |     92.94.95.99 | different data 3 |***
|  6 | asdfh1    |       1 | CentOS  |          7 |         5.5.5.5 |  some new data 1 |
|  7 | asdfh1    |       1 | CentOS  |          7 |         5.5.5.5 |  some new data 2 |
|  8 | asdfh1    |       1 | CentOS  |          7 |         2.2.2.2 |  some new data 3 |###
+----+-----------+---------+---------+------------+-----------------+------------------+

I want to join first and second table to show user "important" information like this:

Your Active Servers:

+-----------+-------------+---------+------------+-----------------+
| server_id | server_name | os_name | os_version |   external_ip   |
+-----------+-------------+---------+------------+-----------------+
|         0 | My Server 1 | Fedora  |         27 |     92.94.95.99 |***
|         2 | My Server 5 | CentOS  |          7 |         2.2.2.2 |###
+-----------+-------------+---------+------------+-----------------+

If I join first and second table by user_id it will load duplicates too but I want only last inserted record of that particular user's server. In this case on last record "external_ip" of one server changed but other one remained the same (marked with *** and ###).

Upvotes: 2

Views: 73

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

you could use an inner join on subselect for the max id for user, server on monitorig 

  select s.server_id, s.server_name, m.os_name, m.os_version, m.external_ip
  from servers s
  inner join Monitoring  m  on m.user_id = s.user_id  and s.auth_code = m.auth_code
  inner  join (
    select max(id) max_id,  user_id, os_name, os_version
    from Monitoring
    group by  user_id, os_name, os_version
  ) t  on t.max_id =  m.id
  where user_id = 1

Upvotes: 1

Related Questions