0

This sql command works fine in sqlitemanager but in my android application this don't sort desc...

select t._id,  u.name, c.commdate, c.message
from tickets t, users u, comments c
where c.userid = u._id and c.ticketid = t._id
and t.status = 5
group by t._id
having max(c.commdate)
order by c.commdate desc
plasmaTonic
  • 345
  • 2
  • 7
  • 22
mcampos
  • 3
  • 2

1 Answers1

0

I have a feeling the "having" part is a problem. Try (untested);

SELECT t._id, u.name, c.commdate, c.message
FROM tickets t
JOIN comments c ON (t._id = c.ticketid)
-- Find most decent comments on tickets, but handle lack of any comments
LEFT JOIN (
    SELECT c2.ticketid, MAX(c2.commdate) as max_commdate
    FROM comments c2
) AS latest ON (c.ticketid = latest.ticketid AND c.commdate = latest.max_commdate)
JOIN users u ON (c.userid = i._id)
WHERE t.status = 5
ORDER BY t._id DESC, c.commdate DESC
Rory Hunter
  • 3,425
  • 1
  • 14
  • 16
  • I/Database(15961): sqlite returned: error code = 1, msg = no such column: latest.commdate – mcampos Apr 09 '12 at 13:47
  • Whoops - SQL amended, try again. – Rory Hunter Apr 09 '12 at 13:50
  • the SQL worked bu still not ordered by commdate it's ordered by ticket id :((( – mcampos Apr 09 '12 at 13:54
  • It should be ordered by both - first by ticket id, and then by commdate. If you only want it ordered by commdate, just take out the first ORDER BY clause. – Rory Hunter Apr 09 '12 at 13:55
  • created a view with the previous query (without the order by) then query the view with mDb.query("solved", null, null, null, null, null, KEY_C_DATE + " DESC "); – mcampos Apr 12 '12 at 13:22