[SOLVED] Groupwise max, how do I add an extra WHERE...?

[fridday]

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!!!

[mwasif]

Change tor1.name='%query%'

to

tor1.name LIKE '%query%'

[fridday]

GREAT! Thanks!! Of course!