I've been fighting with this for a couple days now. I've been searching like mad, and thought I found solutions, but nothing seems to work. I think I may have reached the limit of my understanding :)
This is just a simplified example of what I'm going after: SELECT products.id, products.name, images.src FROM products INNER JOIN items ON items.product_id = products.id LEFT JOIN images ON images.item_id = items.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name, images.position DESC I've also tried things like: SELECT products.id, products.name, images.src FROM products INNER JOIN items ON items.product_id = products.id LEFT JOIN (SELECT * FROM images ORDER BY position DESC) images ON images.item_id = items.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name and... SELECT products.id, products.name, (SELECT images.src FROM images WHERE images.item_id = items.id ORDER BY images.position DESC LIMIT 0,1) FROM products INNER JOIN items ON items.product_id = products.id WHERE items.buyable = 1 GROUP BY products.id ORDER BY products.name Without the GROUP BY, the ordering is correct. I found this example to order for GROUP_CONCAT, but I don't understand how I could translate it for my problem: SELECT ID, GROUP_CONCAT(Val) FROM ( SELECT ID, Val FROM YourTable ORDER BY ID, Val ); Is this a fairly simple problem and solution? What would you search for to find solutions to this? I'm having a heck of a time. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
