postgresql - Multiple Tables Ordered GROUP BY or Distinct SQL -

i've been trying handle on this, no luck, it'd great if can me.

i've got 2 tables. items , item flags.

item

id        name       levelid        containertypeid        roomid        userid      timestamp --        ----       -------        ---------------        ------        ------      ----- 1         item 1     0              0                      3             5           1365072108 2         item 2     0              0                      3             5           1365342108 3         item 3     0              0                      3             5           1389072108 4         item 4     0              0                      3             5           1365085108 5         item 5     2              3                      3             5           1365962108 6         item 6     1              3                      3             5           1365322108 

item_flags

id        itemid     flagid        userid        timestamp --        ----       -------       -------       --------- 1         1          1              5            1365072108 2         1          5              5            1365342108 3         1          2              5            1389072108 4         1          3              5            1365085108 5         2          4              5            1365762108 6         2          1              5            1365921087 7         2          3              5            1365252108 8         3          6              5            1365982108 9         3          7              5            1365312108 10        3          8              5            1365342108 

the goal

what i'm trying list of items, sorted descending timestamp. so, effectively, want list of items based on latest timestamp when either updated actual item or added flag item. application updates timestamp each time flag added against particular item or whenever item name or level updated. problem cannot run group if want name, levelid, etc. fields because have use them in aggregate function, real value not come back. if don't group them, multiple results each item depending on how many flags has. here query:

select                  items.id,                 items.name,                    items.levelid,                 items.containertypeid,                 items.roomid,                 items.userid                          items         left outer join item_flags on                 items.id = flags.itemid                         items.levelid = 0 ,                  items.containertypeid = 0 ,                  items.room = 3 ,                 items.userid = 5         group                 items.id         order                 items.timemodified desc                item_flags.timemodified desc 

i can work if wrap of select fields except items.id in aggregate functions , remove order altogether, need use fields when them , need them ordered! i'm not sure @ point if logic in query makes sense because whilst saying flags.itemid equal items.id, there 1 many relationship there.

i've had @ sql query - grouping multiple tables, there isn't order there , can't figure out how make work!

using pg sql.

thanks!

==================================

as turns out, @ end of all, query had work mysql , not work. we've come different solution i'm running tests on post have, in case might useful else.

you can use following group by

group             items.id,             items.name,                items.levelid,             items.containertypeid,             items.roomid,             items.userid 

since items.id unique, group records same "group items.id".

this might seem more work database, it's database figure out can check id grouping.