SQL query – Unique item with latest date of entry

15 pts.
Tags:
Aggregate function
Distinct
MAX
Microsoft Access
Query
SQL
Subquery
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 multiples 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 to use 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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

<pre>SELECT field1, max(date) as dt
from TableName
group by field1</pre>

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SmurkyIS
    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.
    15 pointsBadges:
    report
  • FrankKulash
    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.
    1,240 pointsBadges:
    report
  • Mafacrofos
    YEEEEEEAAAAAAAAAA
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following