小组由不给我最新的小组 [英] Group by doesn't give me the newest group

问题描述

我试图从我的桌子上得到不同的结果，并且有人说我应该使用该组。这工作的一半...我现在得到不同的结果，但结果不是最新的线程...我的表包含来自几个建筑物的公寓的状态。公寓可以被发现多次，因为它是一个历史表...我需要做一个选择，检索与当前状态不同的公寓。

  ID大厦Apartment_id状态
 1 1 1 1 
 2 1 1 2 
 3 2 2 3 
 4 2 4 2 
 5 2 3 2 
 6 2 5 1 
 7 2 6 1 
  

我目前正在与：

  SELECT * FROM`ib30_history` GROUP BY apartment_id，building ORDER BY id DESC 
  
 
 
 
 
 $ b，Appartment_id 
，状态
从ib30_history a 
 WHERE id =（SELECT MAX（id）FROM ib30_history b 
 WHERE b.Building = a.Building AND b.Appartment_id = a.Appartment_id） 
  

I was trying to get the distinct result from my table, and people said I should use the group by. This worked half way... I now get the distinct result but the result is not the newest thread... my table contains status on apartments from several buildings. The apartments can be found many times since it's a history table... I need to make a select that retrieves the distinct apartments with the current status.

ID    Building Apartment_id  Status
1     1        1             1
2     1        1             2
3     2        2             3
4     2        4             2
5     2        3             2
6     2        5             1
7     2        6             1

I'm currently working with:

SELECT * FROM `ib30_history` GROUP BY apartment_id, building ORDER BY id DESC

解决方案

SELECT 
  Building
  , Appartment_id
  , Status 
FROM ib30_history a
WHERE id = ( SELECT MAX(id) FROM ib30_history b 
             WHERE b.Building = a.Building AND b.Appartment_id = a.Appartment_id)

这篇关于小组由不给我最新的小组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！