Can you better explain your intent? Why are you grouping? This is normally for creating sums, averages, counts etc.
Do you have a small sample of input vs output desired? On 11/10/2010 11:11 AM, James wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
