Although it is easier to read, it can potentially return the wrong results.
Consider this, if a future dated record is entered into the article table in anticipation of some transaction, the having max() clause will return that record instead of the current record. If the having clause can calculate the relative date like: having(a.posted <= sysdate) then this may work. Try both, see what results you get. Good luck! Greg M. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 2:42 PM To: SQL Subject: RE: first record from each category Just add a goup by and a having clause to your query. The last one sent to you should work, but this is much more efficient and easier to read! SELECT A.id, A.categoryId, A.userId, A.title, A.posted, U.alias, C.name FROM article A, user U, category C WHERE A.userId = U.id AND A.categoryId = C.id Group By A.CategoryID Having Max(A.Posted) ORDER BY C.name Thank You, Christian Watt Webmaster SkillPath Seminars [EMAIL PROTECTED] -----Original Message----- From: Phillip B [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 19, 2003 1:12 PM To: SQL Subject: first record from each category I have a query that will give me all the records in all the categories. No I would like to get the newest record in each category. I'm using cf 4.5 and access for the db. Any suggestions? Here is the query to get all the records. SELECT A.id, A.categoryId, A.userId, A.title, A.posted, U.alias, C.name FROM article A, user U, category C WHERE A.userId = U.id AND A.categoryId = C.id ORDER BY C.name Phillip B. www.LoungeRoyale.com www.FillWorks.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Get the mailserver that powers this list at http://www.coolfusion.com
