JQL Query To Show One Issue From Each User On The Board

Question

I've tried looking on the JIRA website and through the JQL Documentation. We've got a Kanban board that we're using, and ideally, I'd like it to show the highest priority issue per user that has an issue in the column before sorting in any other way.

Right now, I've got it ordered by priority DESC, and it will show the following:

 - UserA P1
 - UserA P2
 - UserB P2
 - UserB P3
 - UserC P3
 - UserC P3
 - UserA P4
 - UserB P4

What I would like to see, ideally, would be this (using the same data):

 - UserA P1
 - UserB P2
 - UserC P3
 - Rest of tickets ordered by priority

Is that possible to do in JQL?

Answer 1

I don't think you have an option to do it in JQL but you can directly access the jira database.

You can get some more information about the database schema here

JIRA Database schema

For example this is the description of jiraissue table

mysql> desc jiraissue;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| ID                   | decimal(18,0) | NO   | PRI | NULL    |       |
| pkey                 | varchar(255)  | YES  | UNI | NULL    |       |
| PROJECT              | decimal(18,0) | YES  | MUL | NULL    |       |
| REPORTER             | varchar(255)  | YES  |     | NULL    |       |
| ASSIGNEE             | varchar(255)  | YES  | MUL | NULL    |       |
| issuetype            | varchar(255)  | YES  |     | NULL    |       |
| SUMMARY              | varchar(255)  | YES  |     | NULL    |       |
| DESCRIPTION          | longtext      | YES  |     | NULL    |       |
| ENVIRONMENT          | longtext      | YES  |     | NULL    |       |
| PRIORITY             | varchar(255)  | YES  |     | NULL    |       |
| RESOLUTION           | varchar(255)  | YES  |     | NULL    |       |
| issuestatus          | varchar(255)  | YES  |     | NULL    |       |
| CREATED              | datetime      | YES  |     | NULL    |       |
| UPDATED              | datetime      | YES  |     | NULL    |       |
| DUEDATE              | datetime      | YES  |     | NULL    |       |
| RESOLUTIONDATE       | datetime      | YES  |     | NULL    |       |
| VOTES                | decimal(18,0) | YES  |     | NULL    |       |
| WATCHES              | decimal(18,0) | YES  |     | NULL    |       |
| TIMEORIGINALESTIMATE | decimal(18,0) | YES  |     | NULL    |       |
| TIMEESTIMATE         | decimal(18,0) | YES  |     | NULL    |       |
| TIMESPENT            | decimal(18,0) | YES  |     | NULL    |       |
| WORKFLOW_ID          | decimal(18,0) | YES  | MUL | NULL    |       |
| SECURITY             | decimal(18,0) | YES  |     | NULL    |       |
| FIXFOR               | decimal(18,0) | YES  |     | NULL    |       |
| COMPONENT            | decimal(18,0) | YES  |     | NULL    |       |
+----------------------+---------------+------+-----+---------+-------+

To achieve ranking you can use the following query in MySQL

SELECT ASSIGNEE,
       PRIORITY,
       MAX(rank)
FROM (SELECT  ASSIGNEE,
              PRIORITY, 
              CASE 
                WHEN @prevRank = SUBSTR(PRIORITY, 2) THEN @curRank 
                WHEN @prevRank := SUBSTR(PRIORITY, 2) THEN @curRank := @curRank + 1
              END AS rank
      FROM jiraissue, 
      (SELECT @curRank :=0, @prevRank := NULL) r
      ORDER BY PRIORITY) t
GROUP BY ASSIGNEE 

Refer to this SQLFiddle