-- ## 14: Popular Voted Tickets ## --

-- Tickets ordered by number of votes (>= 1)
-- You must be [/prefs registered] to vote.

SELECT p.value AS __color__,
   t.type AS type, id AS ticket, sum(v.vote) as votes, summary, component,
   t.version, milestone, t.time AS created, t.changetime AS _changetime,
   t.description AS _description, reporter AS _reporter
  FROM ticket t, enum p, votes v
  WHERE status <> 'closed'
AND p.name = t.priority AND p.type = 'priority'
AND v.realm = 'ticket' AND v.resource_id =  CAST(t.id as text)
GROUP BY id, summary, component, t.version, milestone, t.type, owner,
  t.time, t.changetime, t.description, reporter, p.value, status
HAVING sum(v.vote) >= 1
 ORDER BY votes DESC, milestone, t.type, t.time