Home > IT Answers > Development > Sql Query - Unique item with latest date of e...
SmurkyIS
15 pts.
 Sql Query - Unique item with latest date of entry
SQL, Microsoft Access, Query, Subquery, Distinct, Aggregate function, MAX
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?
ASKED: Dec 17, 2007  6:44 PM GMT
UPDATED: February 17, 2012  8:46:05 PM GMT
RELATED QUESTIONS
Mrdenny
63,630 pts.
Answer Wiki:
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.
Last Wiki Answer Submitted:  Dec 17, 2007  6:59 PM (GMT)  by  Mrdenny   63,630 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

Denny,
that is really cool and simple logic, but I have to get all of the fields (10 in number) for my final resultant table.

SmurkyIS
 15 pts.
 

Hi,

This will get all the columns associated with the last date for each itemN:

SELECT	*
FROM	tableName	m
WHERE	date =
	(
	SELECT	MAX (date)
	FROM	tableName
	WHERE	itemN	= m.itemN
	)
ORDER BY	itemN;

If there is a tie for the last date in any itemN, all contenders will be selected.
There may be a more elegant way to do this, using TOP.

FrankKulash
 1,240 pts.
 

YEEEEEEAAAAAAAAAA

Mafacrofos
 10 pts.