I've been trying MySQL 4 alpha (Windows build) as I need UNION but can't work out how to get the query I need against the version without the union
Seems I need a sub select but that's not supported yet. I'm trying to get a score of a search result - the first query works as required (but potentially takes forever), the second is very fast but the 'score' result is not correct (it's always 1) from select(count) rather than an aggregate. Any ideas please? 1) select count(docs.docid) as score, docs.docid, docs.docsection, docs.docsubdir, docs.docmsgid, docs.sendername, DATE_FORMAT(docs.docdate, '%W %M %D %Y') as thedate, doctitle from docs, wdocid where (wordid=450 or wordid=398 or wordid=448) and wdocid.docid = docs.docid and docs.docdate > FROM_UNIXTIME (975228812) GROUP by docs.docid ORDER by score DESC, docdate DESC LIMIT 0, 10 2) select count(docs.docid) as score, docs.docdate, docs.docid, docs.docsection, docs.docsubdir, docs.docmsgid, docs.sendername, DATE_FORMAT(docs.docdate, '%W %M %D %Y') as thedate, doctitle from docs, wdocid_pa where wdocid_pa.docid = docs.docid and docs.docdate > FROM_UNIXTIME(975228544) AND wdocid_pa.wordid=495 GROUP by docs.docid UNION ALL select count(docs.docid) as score, docs.docdate, docs.docid, docs.docsection, docs.docsubdir, docs.docmsgid, docs.sendername, DATE_FORMAT (docs.docdate, '%W %M %D %Y') as thedate, doctitle from docs, wdocid_ca where wdocid_ca.docid = docs.docid and docs.docdate > FROM_UNIXTIME(975228544) AND wdocid_ca.wordid=136 GROUP by docs.docid UNION ALL select count(docs.docid) as score, docs.docdate, docs.docid, docs.docsection, docs.docsubdir, docs.docmsgid, docs.sendername, DATE_FORMAT(docs.docdate, '%W %M %D %Y') as thedate, doctitle from docs, wdocid_bo where wdocid_bo.docid = docs.docid and docs.docdate > FROM_UNIXTIME(975228544) AND wdocid_bo.wordid=1646 GROUP by docs.docid ORDER by score DESC, docdate DESC, docid DESC LIMIT 0, 10 TIA Tony --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
