Max function not working 2

[vba317]

I am trying to write a select query that will give me the only the most current record from the table that I am selecting from. Currently from the query that I have written I would expect 18 records and that is what I get.

SELECT aid,eid,slid,tid
FROM rptdata_monthly.dbo.rpt_dat_CSDetail
WHERE uci='HMF' and rptpd =395 and aid= 7425
GROUP BY  aid,eid,slid,tid,
ORDER BY aid,eid,slid,tid

results

aid eid slid tid
7425 15 73 76
7425 15 73 77
7425 15 73 78
7425 15 73 79
7425 15 73 80
7425 15 73 91
7425 16 74 81
7425 16 74 82
7425 16 74 83
7425 16 74 84
7425 16 74 85
7425 16 74 92
7425 17 75 86
7425 17 75 87
7425 17 75 88
7425 17 75 89
7425 17 75 90
7425 17 75 92

Using the max function I get the same 18 records

SELECT aid,max(eid) as eid,max(slid)as slid,max(tid) as tid
FROM rptdata_monthly.dbo.rpt_dat_CSDetail
WHERE uci='HMF' and rptpd =395 and aid= 7425
GROUP BY  aid,eid,slid,tid
ORDER BY aid,eid,slid,tid

If the max function was working I would expect to only have one record. The one where eid=17, slid =75 and tid =92. Any help is appreciated.

 

[dhookom]

Remove all the extra GROUP BY columns

SELECT aid,max(eid) as eid,max(slid)as slid,max(tid) as tid
FROM rptdata_monthly.dbo.rpt_dat_CSDetail
WHERE uci='HMF' and rptpd =395 and aid= 7425
GROUP BY  aid
ORDER BY aid

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

Thanks that worked perfectly.

 

[Olaf Doschke]

What you should know:
Grouping by all columns you specify a finer granularity for groups. This ends up with each record being a group. MAX is computed per group, so max of one value is that value.

Bye, Olaf