Need some help with some MySQL trickery - Printable Version
-Shoutbox (https://shoutbox.menthix.net)
+-- Forum: MsgHelp Archive (/forumdisplay.php?fid=58)
+--- Forum: Skype & Technology (/forumdisplay.php?fid=9)
+---- Forum: Tech Talk (/forumdisplay.php?fid=17)
+----- Thread: Need some help with some MySQL trickery (/showthread.php?tid=85834)
Need some help with some MySQL trickery by MeEtc on 09-08-2008 at 01:28 AM
After a lot of tinkering around with this query, I'm still having some difficulty getting MySQL to return the data that I want it to.
Some background info:
database structure of the parts in question
I have the following query:
code:
SELECT `notes`, `itemid` FROM yass_log WHERE itemid IN
(SELECT `itemid` FROM yass_image_items WHERE `uid` = 1)
AND `notes` != ''
Results in the following data:
code:
notes itemid
its a MS font 1
i made this! :D 2
its good (Y) 3
its too ugly 4
nice 5
foobar 52
barfoo 52
What I am trying to to accomplish is to return only the last note written for a particular itemid. I have been using itemid 52 as my test. Adding GROUP BY itemid removes the multiple notes for any one itemid, but will only show the first note (in the sample data, 'foobar'). What I want to happen is to have the last note be returned in the data set ('barfoo')
I have tried adding many combinations in an ORDER BY clause, but each time 'foobar' keeps being returned.
Can anyone help out?
RE: Need some help with some MySQL trickery by MeEtc on 09-08-2008 at 02:21 AM
Its been answered in IRC, thanks to Adeptus
code:
SELECT a.`notes`, a.`itemid`
FROM yass_log a
JOIN
(SELECT MAX(`logid`) AS logid
FROM yass_log
GROUP BY `itemid`) b
ON a.`logid` = b.`logid`
JOIN yass_image_items c
ON a.`itemid` = c.`itemid`
WHERE c.`uid` = 1
AND a.`notes` <> ''
RE: Need some help with some MySQL trickery by Dempsey on 09-08-2008 at 07:40 AM
Ah well done Adeptus! He helped me out with some SQL stuff before 