30. juli 2004 - 13:34Der er
18 kommentarer og 1 løsning
if i query
Jeg har lidt problemer med at få sorteret mine udtræk korrekt
Min query: ----------------- SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, MAX(debate2.time) AS last_time FROM $DB.$table debate, $DB.user user LEFT JOIN $DB.$table debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY last_time DESC
I nogle tilfælde kan last_time godt være NULL, og i disse tilfælde skal debate.time sættes i stedet for last_time...
SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, MAX(debate2.time) AS last_time FROM $DB.$table debate, $DB.user user LEFT JOIN $DB.$table debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY IF(last_time IS NULL,debate_time DESC, last_time DESC)
SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, MAX(debate2.time) AS last_time FROM $DB.$table debate, $DB.user user LEFT JOIN $DB.$table debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY IF(last_time IS NULL, debate_time, last_time) DESC
SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, MAX(debate2.time) AS last_time FROM $DB.$table debate, $DB.user user LEFT JOIN $DB.$table debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY IF(MAX(debate2.time) IS NULL, debate_time, MAX(debate2.time)) DESC
du kan selv prøve at logge ind på min sql server og se :)
den oprindelige query: SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, MAX(debate2.time) AS last_time FROM debate_generelt debate, user user LEFT JOIN debate_generelt debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY last_time DESC
SELECT debate.id, debate.subject, debate.time, COUNT( debate2.reply_id ) AS replies, user.name, MAX( debate2.time ) AS last_time, IFNULL( MAX( debate2.time ) , debate.time ) as temp FROM debate_genereltdebate, useruser LEFT JOIN debate_genereltdebate2 ON debate.id = debate2.reply_id WHERE debate.user_id = user.id && debate.reply_id = 0 GROUP BY debate.thread_id ORDER BY temp DESC
Eller bare sådan her uden "MAX( debate2.time ) AS last_time" da den jo er i IFNULL :p
SELECT debate.id, debate.subject, debate.time, COUNT(debate2.reply_id) AS replies, user.name, IFNULL(MAX(debate2.time), debate.time) AS last_time FROM $DB.$table debate, $DB.user user LEFT JOIN $DB.$table debate2 ON debate.id=debate2.reply_id WHERE debate.user_id=user.id && debate.reply_id=0 GROUP BY debate.thread_id ORDER BY last_time DESC
ok, du skal have mange tak for hjælpen.. lav et svar ;)
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
