0

How can I create a column that has ranked the information of the table based on two or three keys? For example, in this table the rank variable is based on Department and Name:

Dep | Name | Rank 
----+------+------
 1  | Jeff |  1
 1  | Jeff |  2
 1  | Paul |  1
 2  | Nick |  1
 2  | Nick |  2

I have found this solution but it's in SQL and I don't think it applies to my case as all information is in one table and the responses seem to SELECT and JOIN combine information from different tables. Thank you in advance

Question
  • 3
  • 3
  • Please provide your desired output for the example data. – CL. Jul 18 '17 at 18:22
  • How is the rank determined from the two relevant columns? Especially since identical values give two different ranks for Jeff and Nick. – Yunnosch Jul 18 '17 at 19:13
  • Hi @CL. my desired output is column `Rank` and @Yunnosch the rank is determined based on Department and Name. So there may be multiple Nicks in many departments but the Rank column needs to rank each Nick on each department separately – Question Jul 24 '17 at 09:56

1 Answers1

1

You can count how many rows come before the current row in the current group:

UPDATE MyTable
SET Rank = (SELECT COUNT(*)
            FROM MyTable AS T2
            WHERE T2.Dep    = MyTable.Dep
              AND T2.Name   = MyTable.Name
              AND T2.rowid <= MyTable.rowid);

(The rowid column is used to differentiate between otherwise identical rows. Use the primary key, if you have one.)

CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thank you it worked perfectly. I had to create a `rowid` column first which I didn't have so far with this `CREATE TABLE MyTable_ID (id integer primary key autoincrement, department, person); INSERT INTO MyTable_ID (department, person) SELECT department, person FROM MyTable_original;` And then I created an extra column in the table MyTable_ID `ALTER TABLE MyTable_ID ADD COLUMN Rank;` For the code from @CL. to work one needs to rename the table MyTable_ID into MyTable – Question Jul 24 '17 at 14:15
  • can you @CL. please explain why you use T2, why a temporary table T2 needs to be used in order to calculate a column within an existing table? – Question Jul 24 '17 at 14:16
  • There is no temporary table. AS creates an alias for the table name, because the subquery needs to refer to values in two different rows. – CL. Jul 24 '17 at 14:41