Nyeste billede med GROUP BY

Du kan brugr "order by date_of_entry desc"

Jeg prøver:

SELECT MAX(ObjectPicture.picture_fk) AS picture_id,MAX(category_fk) AS category_id
FROM Object, ObjectPicture, Picture
WHERE ObjectPicture.object_fk=Object.id AND ObjectPicture.picture_fk = Picture.id
GROUP BY category_fk ORDER BY date_of_entry DESC

Det giver mig en fejl: Column name 'Object.date_of_entry' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

Så jeg group'er by date_of_entry:
SELECT MAX(ObjectPicture.picture_fk) AS picture_id,MAX(category_fk) AS category_id
FROM Object, ObjectPicture, Picture
WHERE ObjectPicture.object_fk=Object.id AND ObjectPicture.picture_fk = Picture.id
GROUP BY category_fk,date_of_entry ORDER BY date_of_entry DESC

Ingen fejl -- men nu får jeg samme category_id flere gange.. det ser ud til at jeg får et billede pr. unikke dato.. ?

Hvis jeg bruger datoen i en funktion i stedet:
SELECT MAX(ObjectPicture.picture_fk) AS picture_id,MAX(category_fk) AS category_id,
MAX(date_of_entry) AS date
FROM Object, ObjectPicture, Picture
WHERE ObjectPicture.object_fk=Object.id AND ObjectPicture.picture_fk = Picture.id
GROUP BY category_fk,date_of_entry ORDER BY date_of_entry DESC

Det ser ud til at give samme resultat, bare med en dato på ..

Hvad med denne:

SELECT picture_fk, max(Object.date_of_entry)
FROM Object LEFT JOIN ObjectPicture ON Object.id = ObjectPicture.object_fk
GROUP BY Object.category_fk

Prøv med følgende:
Prøv denne

select picture_fk
from ObjectPicture join object on object.id = ObjectPicture.object_fk
where object.date_of_entry in
(select max(date_of_entry)
from object)

Hvis flere objects kan have samme date_of_entry, kan du vælge en tilfældig af disse med

select top 1 picture_fk

Jeg vil helst ikke bruge nestede SELECTs når det ikke er nødvendigt.

Anyway, jeg har løst det i Access' view-design.