SQL "Group By" VarChar Field With Max Date or All results of the same date

Question

Hello,

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

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!

Answer 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

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

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