group by分组后获得每组中符合条件的那条记录

当group by单独使用时，只显示出每组的第一条记录。
如下，未分组时查询出两条记录

SELECT
  info.id,
  info.switch_id,
  info.port_id,
  info.mac_addr,
  info.ip,
  info.rec_time,
  info.dev_name,
  info.is_active,
  info.port_type,
  info.real_type,
  cfg.port_describe,
  cfg.port_value,
  switchers.name,
  switchers.switch_ip
FROM
  (
    T_NET_SWITCHBOARD_PORT_INFO AS info
    LEFT JOIN T_NET_SWITCHBOARD_PORT_CFG AS cfg
      ON info.port_id = cfg.port_id
  )
  LEFT JOIN T_NET_SWITCHBOARD_CFG AS switchers
    ON info.switch_id = switchers.switch_id
WHERE info.mac_addr != ''
  AND info.`real_type` = 0
  AND info.port_type != 1
  AND info.dev_name LIKE "%"  "%"
  AND info.ip LIKE "%" "%"
  AND info.mac_addr LIKE "%"  "%"
  AND info.mac_addr LIKE '%00:10:7F:52:0D:FD%'

结果 

  id       switch_id  port_id  mac_addr               ip                         rec_time                 dev_name  is_active  port_type  real_type  port_describe          port_value  name                           switch_ip      
------  ---------  -------  -----------------  --------------  -------------------  --------  ---------  ---------  ---------  ---------------------  ----------  ----------------------------- 
 59591         18     3697  00:10:7F:52:0D:FD   168.160.15.197   2018-08-08 15:40:32                    0          2          0  GigabitEthernet5/0/2           33  地下UPS室-华为S9306                 168.160.15.66  
 59798         20     3758  00:10:7F:52:0D:FD                            2018-08-08 15:47:16                    0          0          0  GigabitEthernet0/0/14          19  地下总控室-华为S5700-1                168.160.15.76 

这个时候加上group by 默认加载显示第一条，如果我想要port_type最小的那条该如何实现？

 

先对分组前进行排序，然后再分组获取。

SELECT a.* FROM (SELECT
  info.id,
  info.switch_id,
  info.port_id,
  info.mac_addr,
  info.ip,
  info.rec_time,
  info.dev_name,
  info.is_active,
  info.port_type,
  info.real_type,
  cfg.port_describe,
  cfg.port_value,
  switchers.name,
  switchers.switch_ip
FROM
    T_NET_SWITCHBOARD_PORT_INFO AS info
  LEFT JOIN T_NET_SWITCHBOARD_PORT_CFG AS cfg
      ON info.port_id = cfg.port_id 
  LEFT JOIN T_NET_SWITCHBOARD_CFG AS switchers
      ON info.switch_id = switchers.switch_id
WHERE (info.mac_addr != ''
  AND info.`real_type` = 0
  AND info.port_type != 1
  AND info.mac_addr LIKE '%00:10:7F:52:0D:FD%')
  ORDER BY info.port_type -- 先排序再分组
  ) a
GROUP BY  a.mac_addr 
ORDER BY 
a.switch_ip,
a.port_describe