ToonMariner Posted January 1, 2008 Share Posted January 1, 2008 OK I have no life until this gets done so pretty please help me out!!!! I have a table or items and I need to select all fields from the table but distinct values from just one field eg. many items and numerous occuencies of a category - I want the last entry from each category. Any help much appreciated.... (I know I have done this before but just can't get it...) Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/ Share on other sites More sharing options...
drranch Posted January 1, 2008 Share Posted January 1, 2008 Try this out... SELECT * FROM tablename ORDER BY id DESC LIMIT 1 HA your not the only one with out a life. Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427111 Share on other sites More sharing options...
ToonMariner Posted January 1, 2008 Author Share Posted January 1, 2008 sorry didn't make it clear... There are many categories 1,2,3,4,5,6,7,8,9 etc etc and multiple entires in each category... so there could be 20 in cat 3 and 17 in cat 8 what I need is the last entry from each category. PS I also need to select data from a couple of other tables in the query too but when ever I use more than one field in the select part for the query and have a DISTINCT on one of them the query fails.... Sorry for not being clear. Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427117 Share on other sites More sharing options...
drranch Posted January 1, 2008 Share Posted January 1, 2008 What is your current syntax. Reading syntax helps me to better understand what your looking for. Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427123 Share on other sites More sharing options...
ToonMariner Posted January 1, 2008 Author Share Posted January 1, 2008 SELECT `media`.`media_id` , `media`.`user_id` , `media`.`media_key` , `media`.`type` , `media`.`title` , DISTINCT(`media`.`categories`) , `user`.`username` , `sections`.`url` , DATEDIFF(NOW(), `media`.`added`) AS `period` FROM `media` , `user` , `sections` WHERE `media`.`flag` != 'm' AND `user`.`user_id` = `media`.`user_id` AND `user`.`active` = 'y' ORDER BY `media`.`added` DESC Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427146 Share on other sites More sharing options...
Barand Posted January 1, 2008 Share Posted January 1, 2008 try SELECT `media`.`media_id` , `media`.`user_id` , `media`.`media_key` , `media`.`type` , `media`.`title` , `media`.`categories` , `user`.`username` , `sections`.`url` , DATEDIFF(NOW(), `media`.`added`) AS `period` FROM `media` , `user` , `sections`, (SELECT categories, MAX(added) as latest FROM media GROUP BY categories) as x WHERE `media`.`flag` != 'm' AND `user`.`user_id` = `media`.`user_id` AND `user`.`active` = 'y' AND media.categories = x.categories AND media.added = x.latest ORDER BY `media`.`added` DESC Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427229 Share on other sites More sharing options...
ToonMariner Posted January 1, 2008 Author Share Posted January 1, 2008 Barand... Thanks for the reply... That query (I think) does the business but returns multiple duplicate entries. I'd love a little precis on what the query is doing as I 'kind of' get what its doing but not totally... ANyway and update... I tried this... SELECT DISTINCT(`media`.`categories`) , `media`.* , `user`.`username` , `sections`.`url` , DATEDIFF(NOW(), `media`.`added`) AS `period` FROM `media` , `user` , `sections` WHERE `media`.`flag` != 'm' AND `user`.`user_id` = `media`.`user_id` AND `user`.`active` = 'y' GROUP BY `media`.`categories` ORDER BY `media`.`added` DESC with some success (it appears the distinct coming first in the select clause is important). How ever it selects the earliest records in the dataset rather than the latest. Would anyone be so kind as to suggest why? Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427238 Share on other sites More sharing options...
Barand Posted January 1, 2008 Share Posted January 1, 2008 SELECT a, b, c, SUM(d) FROM table GROUP BY a When using GROUP BY you should group by all selected columns that are not aggregated (ie not SUM(), COUNT() etc) and SQL normally enforces this. MySQL is lenient and lets you group by a single col as in the example above. In this case the manual states that the values of b and c will be unpredictable but they usually are taken from the first record in each group. + + + You can think of the subquery as a temporary table called x which contains the latest date for each category from the media table [pre] media subquery id cat date cat maxdate ----+-----+------------- -----+------------- 1 1 2007-01-01 2 1 2007-01-02 3 1 2007-01-03 <---> 1 2007-01-03 4 2 2007-01-01 5 2 2007-01-02 6 2 2007-01-04 <---> 2 2007-01-04 [/pre] Joining to this should identify the latest record for each category A thought just occured - if you have several recs on that last date it pulls them all. May have to use MAX(id) in the subquery and match on that instead of date. Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427241 Share on other sites More sharing options...
ToonMariner Posted January 1, 2008 Author Share Posted January 1, 2008 This is the query I ended up with (just a couple of minor alterations in field names etc) and it works like a charm. SELECT `media`.* , `user`.`username` , `sections`.`url` , `sections`.`string_key` , DATEDIFF(NOW(), `media`.`added`) AS `period` FROM `media` , `user` , `sections`, ( SELECT DISTINCT(`categories`) , MAX(`media_id`) as `latest` FROM `media` GROUP BY `categories` ) AS `x` WHERE `media`.`flag` != 'm' AND `user`.`user_id` = `media`.`user_id` AND `user`.`active` = 'y' AND `media`.`categories` = `x`.`categories` AND `media`.`media_id` = `x`.`latest` AND `sections`.`section_id` = `media`.`categories` ORDER BY `media`.`added` DESC MANY THANKS!!!! Quote Link to comment https://forums.phpfreaks.com/topic/83926-solved-its-new-year-and-i-am-stuck/#findComment-427260 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.