Question

Sql Query - Unique item with latest date of entry

SQL, Subquery, Distinct, Aggregate function, MAX, Microsoft Access, Query

Hey guys, I have been working on the query running against MS access database.

The table has multiple columns and two of them (which are vital to this query) are Date and ItemN ( suggesting Item number).

There are tuples which show multiple entries for single itemN, for example.
item 1 has three date entries as compared to item 2 which has one entry, corresponding to one item number.

The query condition is to fetch the distinct item number against its latest entry in date.

I am trying this query

SELECT field name1, field name2,...field name9, max(date) as dt
FROM (SELECT * FROM TABLEname
ORDER BY DATE DESC)
group by fieldname1, fieldname2, ..fieldname9, dt;


Am I doing it right?

You don't need the subquery to do this, and you do not put the "dt" in the group by clause.

SELECT field1, max(date) as dt
 from TableName
 group by field1

You want the lease possible other fields in the SELECT statement. Otherwise you won't get the data you are looking for.