fridday Posted April 12, 2008 Share Posted April 12, 2008 Hello! I would be very happy if there was someone willing to help me, since I'm getting nowhere with this. I'm developing a community driven wiki-style torrent site and just learned how to get the groupwise max from my db, like this: SELECT tor1.id, tor1.name, tor1.date, tor1.torrent_id FROM torrent AS tor1, (SELECT torrent_id, MAX(date) AS latest FROM torrent GROUP BY torrent_id) AS tor2 WHERE tor2.torrent_id = tor1.torrent_id AND tor1.date = tor2.latest Sorry if it's ugly.. I only want to allow INSERT and SELECT, so every time someone make a change to the torrent meta data, it creates a new row using INSERT. The unique row id is "id", and the "torrent_id" is the same for all rows concerning the same torrent. The most up to date version of the torrent meta data is in the row with the latest (highest) date. When searching for torrents, I only want to search in the latest revisions, why I've made the SQL-query above. It successfully selects all the latest revisions of the torrents. But I don't know how to incorporate the search query to that SQL-query! I thought this would do it: SELECT tor1.id, tor1.name, tor1.date, tor1.torrent_id FROM torrent AS tor1, (SELECT torrent_id, MAX(date) AS latest FROM torrent GROUP BY torrent_id) AS tor2 WHERE tor2.torrent_id = tor1.torrent_id AND tor1.date = tor2.latest AND tor1.name='%query%' But it doesn't work... It selects zero rows. I know I'm bad at SQL-syntax, sorry! I'm also sorry if I'm bad at describing my problem! Anyone who can help me get it right? Thanks!!! Quote Link to comment https://forums.phpfreaks.com/topic/100814-solved-groupwise-max-how-do-i-add-an-extra-where/ Share on other sites More sharing options...
mwasif Posted April 12, 2008 Share Posted April 12, 2008 Change tor1.name='%query%' to tor1.name LIKE '%query%' Quote Link to comment https://forums.phpfreaks.com/topic/100814-solved-groupwise-max-how-do-i-add-an-extra-where/#findComment-515568 Share on other sites More sharing options...
fridday Posted April 12, 2008 Author Share Posted April 12, 2008 GREAT! Thanks!! Of course! Quote Link to comment https://forums.phpfreaks.com/topic/100814-solved-groupwise-max-how-do-i-add-an-extra-where/#findComment-515577 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.