SQL show first unique values according to row then second unique value-CodePudding

I have a table like this

Table name: tasks

 --------- ------------- -------------
| Name    | taskName    | issueDate  |
 --------- ------------- -------------
| A       | AA          | 4/11/2022  |
| A       | AB          | 4/11/2022  |
| A       | AC          | 4/11/2022  |
| B       | BA          | 3/11/2022  |
| B       | BB          | 3/11/2022  |
| B       | BC          | 3/11/2022  |
| C       | CA          | 2/11/2022  |
| C       | CB          | 2/11/2022  |
 --------- ------------- -------------

Resulted table will be like this

 --------- ------------- ------------
| Name    | taskName    | issueTime |
 --------- ------------- ------------
| A       | AA          | 4/11/2022 |
| B       | BA          | 3/11/2022 |
| C       | CA          | 2/11/2022 |
| A       | AB          | 4/11/2022 |
| B       | BB          | 3/11/2022 |
| C       | CB          | 2/11/2022 |
| A       | AC          | 4/11/2022 |
| C       | CB          | 2/11/2022 |
 --------- ------------- -----------|

Show the unique name first according to issueTime in descending order.

CodePudding user response：

First give a row number for each 'name' group in the ascending order of issue date and taskname, then sort the result set by the row number.

Query

select t.name, t.taskname, t.issuedate from(
  select row_number() over(
    partition by name
    order by issuedate, taskname
  ) as rn, *
  from tasks
) as t
order by t.rn, t.name;

CodePudding user response：

This query would be useful for the mentioned scenario:

select name, taskname, issuedate from(
select name, taskname, issuedate, row_number() over(partition by name order by issuedate desc) as row_number 
from task)
order by rownum;