SQL“分组依据”具有最大日期或同一日期的所有结果的 VarChar 字段

Question

假设我有这个表，

--------------------------------------------------------------
|  ID  |  DATE  |  GROUPNAME  |  RESULT  |  INFO1  |  INFO2  |
--------------------------------------------------------------
| 1    | 01/06  | Group1      | 12345    | Abc     | xxxx    |
| 2    | 01/04  | Group2      | 54321    | AAA     | zzzz    |
| 3    | 01/03  | Group3      | 11111    | BBB     | zzzz    |
| 4    | 01/06  | Group1      | 22222    | Def     | xxxx    |
| 5    | 01/02  | Group3      | 33333    | CCC     | yyyy    |
--------------------------------------------------------------

我想要进行一个查询，选择每个 groupname 的最大日期，并返回该 groupname 中该日期的所有结果。并按 groupname 排序，

例如，我的结果将是

1 | 01/06 | Group1 | 12345 | Abc | xxxx
4 | 01/06 | Group1 | 22222 | Def | xxxx
2 | 01/04 | Group2 | 54321 | AAA | zzzz
3 | 01/03 | Group3 | 11111 | BBB | zzzz

生成该结果集的有效查询是什么？

谢谢你！

Answer 1

除非我错过了什么：

SELECT t.id,
       t.date,
       t.groupname,
       t.result,
       t.info1,
       t.info2
  FROM TABLE t
  JOIN (SELECT t.groupname,
               MAX(t.date) 'maxd'
          FROM TABLE t
      GROUP BY t.groupname) x ON x.groupname = t.groupname
                             AND x.maxd = t.date
ORDER BY t.groupname

Answer 2

SELECT TABLE.ID, TABLE.DATE, TABLE.GROUPNAME, TABLE.RESULT, TABLE.INFO1, TABLE.INFO2
FROM TABLE INNER JOIN (SELECT GROUPNAME, MAX(DATE) FROM TABLE GROUP BY GROUPNAME) T
ON TABLE.GROUPNAME = T.GROUPNAME AND TABLE.DATE = T.DATE
ORDER BY TABLE.GROUPNAME 

Answer 3

试试这个：

Width (
Select max(Date), groupname
from Table
group gy groupname
) AS T1
Select T2.id,
       T2.Date,
       T2.groupname,
       T2.result,
       T2.info1,
       T2.info2
from T1 
left join Table T2 
on (T1.date = T2.date 
AND T1.groupname = T2.groupname) 
order by groupname