UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Grouping Entire Table To Get Max On A Single Field, Access 2013    
 
   
SomekindaVB
post May 21 2019, 11:57 PM
Post#1



Posts: 298
Joined: 15-December 16



Hi All.

I have a table of about 30 fields. I want to group by a name and then select only the highest corresponding date.

I can get results with this:

SELECT NameField, Max([DateField]) AS [MaxOfDateField]
FROM dbo.Tbl_MyTable
GROUP BY dbo.Tbl_MyTable.NameField

But how do i get the rest of the table fields to display also? I can't use (*)

Thanks
Go to the top of the page
 
cheekybuddha
post May 22 2019, 01:50 AM
Post#2


UtterAccess VIP
Posts: 11,292
Joined: 6-December 03
From: Telegraph Hill


CODE
SELECT
  b.*
FROM (
  SELECT
    NameField,
    Max([DateField]) AS [MaxOfDateField]
  FROM dbo.Tbl_MyTable
  GROUP BY dbo.Tbl_MyTable.NameField
) a
INNER JOIN Tbl_MyTable b
        ON a.NameField = b.NameField
       AND a.MaxOfDateField = b.DateField


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th June 2019 - 01:48 PM