title: Is there a way to do partition by on top of partition by in oracle?
body: <p>I have recently started getting back to ORACLE SQL world. I am trying to figure out something as below.</p>
  <p>Let's say you have <strong>STUDENT</strong> table as below.</p>
  <pre><code>SELECT * FROM STUDENT where Name = 'Jill' order by Rank desc;
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Name</th>
  <th>Group</th>
  <th>Rank</th>
  <th>Priority</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Jill</td>
  <td>S_504</td>
  <td>5</td>
  <td>2</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_504</td>
  <td>5</td>
  <td>2</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_504</td>
  <td>5</td>
  <td>2</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_501_1_A</td>
  <td>5</td>
  <td>3</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_501_1_A</td>
  <td>5</td>
  <td>3</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_501_1_A</td>
  <td>5</td>
  <td>3</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_502_2_A</td>
  <td>4</td>
  <td>4</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_502_2_A</td>
  <td>4</td>
  <td>4</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_502_2_A</td>
  <td>4</td>
  <td>4</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_500</td>
  <td>4</td>
  <td>5</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_500</td>
  <td>4</td>
  <td>5</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_500</td>
  <td>4</td>
  <td>5</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_507_1_A</td>
  <td>3</td>
  <td>6</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_507_1_A</td>
  <td>3</td>
  <td>6</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_507_1_A</td>
  <td>3</td>
  <td>6</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_503_2_A</td>
  <td>3</td>
  <td>7</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_503_2_A</td>
  <td>3</td>
  <td>7</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_503_2_A</td>
  <td>3</td>
  <td>7</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_509</td>
  <td>2</td>
  <td>8</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_509</td>
  <td>2</td>
  <td>8</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_509</td>
  <td>2</td>
  <td>8</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_405</td>
  <td>2</td>
  <td>9</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_405</td>
  <td>2</td>
  <td>9</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_405</td>
  <td>2</td>
  <td>9</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_706</td>
  <td>1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_706</td>
  <td>1</td>
  <td>10</td>
  </tr>
  <tr>
  <td>Jill</td>
  <td>S_706</td>
  <td>1</td>
  <td>10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Above table has one student, which belongs to multiple <strong>Groups</strong>. Now, Each of these groups are having different Ranking of their own - based on which Priority column is populated.</p>
  <p>For e.g. <strong>S_501</strong> and <strong>S_501_1_A</strong> both groups are having same <code>Rank</code>, It is completely Okay if S_501 was given <strong>priority</strong> 3 and S_501_1_A was given <strong>priority</strong> 2 - Meaning for same ranking, order of Priority does not matter.</p>
  <p>You see I have sorted the result set by <strong>Rank</strong> Column in descending order. Just for the sake of understanding. I would like to populate the <strong>Priority</strong> column as shown in above table. Assume the <strong>Priority</strong> column is <strong>yet</strong> to be created or an output.</p>
  <p>I tried <code>1+dense_rank() over (partition by Name, Group order by Rank desc)</code> but it just sets Priority to 2 for all. I don't know what I am doing wrong here.</p>
  <p>I feel like first I have to do <code>partition by Name, Rank</code> and then on top of it I have to apply <code>dense_rank() over (partition by Name, Group Order by Rank DESC)</code>.</p>
  <p>Can you please help me out ? or is there any approach to achieve this ?</p>
  <p><strong>NOTE:</strong> Priority starts with value 2.</p>