You may end up doing some date processing in a subquery to get the current
record for each category.
The following will give you the most current record I think.
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
and a.posted = (select max(b.posted) from article b
where
...create joins on all key fields between A and B
...except for posted, which looks like this:
and a.posted <= sysdate)
ORDER BY
C.name
I assume that posted is a date field. If it isn't then I don't know
how you're going to tell when a value was added to a table.
Good luck.
Greg M.
-----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
