SQL " Group By " VarChar字段，最大日期或所有结果相同的日期

Lets say I have this table

假设我有这张桌子

--------------------------------------------------------------
|  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    |
--------------------------------------------------------------

I want to make a query that selects the max date of each groupname and return all results of that date from that groupname. And order by the groupname

我想做一个查询，选择每个groupname的最大日期，并从该groupname返回该日期的所有结果。并按组名排序

E.g., My result would be

我的结果是。

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

What would be an efficient query to produce that result set?

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

Thank you!

谢谢你们！

3 个解决方案

#1

Unless I'm missing something:

除非我丢失的东西:

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

#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

#3

try this:

试试这个:

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

#1