Rank increase only for change in row value

Question

I've use psql rank and partition functions to create a table that describes the sequence of photos taken on a trip, where id is the picture id, nsid is the photographer id, location is where the photo was taken, and rank is the order in which the photos were taken during the trip (defined by photos in same date_trunc time period).

id  |nsid           |location   |date_taken |time_taken |date_trunc |rank   
1   |10000137@N04   |11         |08/03/2006 |18:42:02   |2006-03-06 |1      
2   |10000137@N04   |11         |08/03/2006 |18:56:44   |2006-03-06 |2      
3   |10000137@N04   |446        |12/09/2007 |01:05:27   |2007-09-10 |1      
4   |10000137@N04   |4          |12/09/2007 |01:05:35   |2007-09-10 |2      
5   |10000137@N04   |4          |12/09/2007 |01:05:41   |2007-09-10 |3      
6   |10000137@N04   |56         |12/09/2007 |01:05:45   |2007-09-10 |4      
7   |10000137@N04   |11         |03/07/2008 |09:21:54   |2008-06-30 |1      
8   |10000137@N04   |3199       |09/03/2013 |16:37:40   |2013-03-04 |1      
9   |10000137@N04   |4          |09/03/2013 |18:56:55   |2013-03-04 |2      
10  |10000280@N07   |215        |02/08/2007 |17:25:43   |2007-07-30 |1      
11  |10000280@N07   |215        |03/08/2007 |19:35:11   |2007-07-30 |2      
12  |100006043@N02  |203        |05/08/2013 |14:02:49   |2013-07-29 |1      
13  |100006043@N02  |1          |05/08/2013 |18:08:45   |2013-07-29 |2      
14  |100010324@N02  |1          |14/04/2013 |19:19:13   |2013-04-08 |1      
15  |100010343@N03  |367        |04/06/2014 |18:54:13   |2014-06-02 |1      
16  |100014982@N03  |2909       |28/08/2013 |23:20:27   |2013-08-26 |1      
17  |100014982@N03  |183        |13/09/2013 |12:26:41   |2013-09-09 |1      
18  |100018928@N06  |1149       |01/08/2013 |08:40:22   |2013-07-29 |1      
19  |100018928@N06  |1149       |01/08/2013 |08:40:23   |2013-07-29 |2      
20  |100018928@N06  |1149       |01/08/2013 |08:40:32   |2013-07-29 |3      
21  |100018928@N06  |1149       |01/08/2013 |08:40:33   |2013-07-29 |4      
22  |100021169@N02  |10         |02/07/2005 |18:17:26   |2005-06-27 |1      
23  |100021169@N02  |10         |02/07/2005 |18:18:13   |2005-06-27 |2

I would like to create a new column called location_rank which is the position in sequence in which the location was visited. Thus photos taken at the same location by the same user user on the same trip would have the same location_rank, and the location_rank value would only increase for each new location visited during that user's trip (i.e. only when the location value changes from the previous row in the partition). So the desired table would look like this:

id |nsid          |location |date_taken|time_taken|date_trunc  |rank|location_rank
1  |10000137@N04  |11       |08/03/06  |18:42:02  |2006-01-01  |1   |1
2  |10000137@N04  |11       |08/03/06  |18:56:44  |2006-01-01  |2   |1
3  |10000137@N04  |446      |12/09/07  |01:05:27  |2007-01-01  |1   |1
4  |10000137@N04  |4        |12/09/07  |01:05:35  |2007-01-01  |2   |2
5  |10000137@N04  |4        |12/09/07  |01:05:41  |2007-01-01  |3   |2
6  |10000137@N04  |56       |12/09/07  |01:05:45  |2007-01-01  |4   |3
7  |10000137@N04  |11       |03/07/08  |09:21:54  |2008-01-01  |1   |1
8  |10000137@N04  |3199     |09/03/13  |16:37:40  |2013-01-01  |1   |1
9  |10000137@N04  |4        |09/03/13  |18:56:55  |2013-01-01  |2   |2
10 |10000280@N07  |215      |02/08/07  |17:25:43  |2007-01-01  |1   |1
11 |10000280@N07  |215      |03/08/07  |19:35:11  |2007-01-01  |2   |1
12 |100006043@N02 |203      |05/08/13  |14:02:49  |2013-01-01  |1   |1
13 |100006043@N02 |1        |05/08/13  |18:08:45  |2013-01-01  |2   |2
14 |100010324@N02 |1        |14/04/13  |19:19:13  |2013-01-01  |1   |1
15 |100010343@N03 |367      |04/06/14  |18:54:13  |2014-01-01  |1   |1
16 |100014982@N03 |2909     |28/08/13  |23:20:27  |2013-01-01  |1   |1
17 |100014982@N03 |183      |13/09/13  |12:26:41  |2013-01-01  |2   |2
18 |100018928@N06 |1149     |01/08/13  |08:40:22  |2013-01-01  |1   |1
19 |100018928@N06 |1149     |01/08/13  |08:40:23  |2013-01-01  |2   |1
20 |100018928@N06 |1149     |01/08/13  |08:40:32  |2013-01-01  |3   |1
21 |100018928@N06 |1149     |01/08/13  |08:40:33  |2013-01-01  |4   |1
22 |100021169@N02 |10       |02/07/05  |18:17:26  |2005-01-01  |1   |1
23 |100021169@N02 |10       |02/07/05  |18:18:13  |2005-01-01  |2   |1
24 |10002296@N02  |25       |12/03/11  |13:41:10  |2011-01-01  |1   |1
25 |10002296@N02  |25       |12/03/11  |13:42:19  |2011-01-01  |2   |1
26 |10002296@N02  |25       |12/03/11  |14:00:49  |2011-01-01  |3   |1
27 |10002296@N02  |25       |12/03/11  |14:07:57  |2011-01-01  |4   |1
28 |10002296@N02  |25       |12/03/11  |14:10:12  |2011-01-01  |5   |1
29 |10002450@N05  |105      |07/08/11  |02:43:45  |2011-01-01  |1   |1
30 |10002450@N05  |5        |07/08/11  |05:19:27  |2011-01-01  |2   |2
31 |100025689@N07 |966      |28/10/13  |20:19:05  |2013-01-01  |1   |1
32 |100025689@N07 |966      |28/10/13  |20:35:38  |2013-01-01  |2   |1
33 |100025689@N07 |966      |28/10/13  |20:35:55  |2013-01-01  |3   |1
34 |100025689@N07 |966      |28/10/13  |21:09:53  |2013-01-01  |4   |1
35 |100025689@N07 |966      |28/10/13  |21:27:50  |2013-01-01  |5   |1
36 |100029993@N07 |831      |27/09/14  |18:27:24  |2014-01-01  |1   |1
37 |100029993@N07 |3708     |09/10/14  |20:36:42  |2014-01-01  |2   |2
38 |100033343@N05 |42       |31/10/14  |12:23:50  |2014-01-01  |1   |1
39 |10003453@N03  |16       |28/08/09  |13:46:44  |2009-01-01  |1   |1
40 |10003453@N03  |186      |28/08/09  |14:17:39  |2009-01-01  |2   |2
41 |10003453@N03  |19       |28/08/09  |16:43:07  |2009-01-01  |3   |3
42 |10003453@N03  |2919     |29/08/09  |12:18:10  |2009-01-01  |4   |4
43 |10003453@N03  |2453     |29/08/09  |13:22:12  |2009-01-01  |5   |5
44 |10003453@N03  |262      |29/08/09  |15:59:14  |2009-01-01  |6   |6
45 |10003453@N03  |22       |30/08/09  |15:26:56  |2009-01-01  |7   |7
46 |10003453@N03  |33       |30/08/09  |16:25:30  |2009-01-01  |8   |8
47 |10003453@N03  |2914     |30/08/09  |21:29:39  |2009-01-01  |9   |9
48 |10003453@N03  |408      |03/09/09  |23:36:12  |2009-01-01  |10  |10
49 |10003453@N03  |133      |06/09/09  |21:57:03  |2009-01-01  |11  |11
50 |10003453@N03  |713      |16/09/09  |00:01:53  |2009-01-01  |12  |12

I've tried using the dense_rank function but I have been unsuccessful. I would be extremely grateful for help in creating the location_rank column.

Here is the dense_rank query that I've tried unsuccesfully:

create table prep.location_rank2 as 
select 
    id, nsid, 
    location,date_taken,time_taken,date_trunc, rank, 
    dense_rank() over(
        partition by nsid, date_trunc 
        order by date_taken, time_taken, location, rank
    ) as location_rank 
from prep.test 
order by nsid; 

Answer 1

SELECT id,
       nsid,
       location,
       date_taken,
       time_taken,
       date_trunc,
       [rank],
       DENSE_RANK() OVER (PARTITION BY nsid, date_trunc 
                              ORDER BY CASE WHEN [rank] = rnum
                                            THEN location
                                            ELSE CAST(CONCAT(CAST([rank]-rnum AS VARCHAR), CAST(location AS VARCHAR)) AS INT)
                                        END) location_rank
  FROM (SELECT *, 
               ROW_NUMBER() OVER (PARTITION BY nsid, date_trunc 
                                      ORDER BY location) rnum
          FROM photo_trip
       ) a
 ORDER BY id;

Result

id  nsid            location    date_taken  time_taken          date_trunc  rank    location_rank
1   10000137@N04    11          2006-03-08  18:42:02.0000000    2006-03-06  1       1
2   10000137@N04    11          2006-03-08  18:56:44.0000000    2006-03-06  2       1
3   10000137@N04    446         2007-09-12  01:05:27.0000000    2007-09-10  1       1
4   10000137@N04    4           2007-09-12  01:05:35.0000000    2007-09-10  2       2
5   10000137@N04    4           2007-09-12  01:05:41.0000000    2007-09-10  3       2
6   10000137@N04    56          2007-09-12  01:05:45.0000000    2007-09-10  4       3
7   10000137@N04    11          2008-07-03  09:21:54.0000000    2008-06-30  1       1
8   10000137@N04    3199        2013-03-09  16:37:40.0000000    2013-03-04  1       1
9   10000137@N04    4           2013-03-09  18:56:55.0000000    2013-03-04  2       2
10  10000280@N07    215         2007-08-02  17:25:43.0000000    2007-07-30  1       1
11  10000280@N07    215         2007-08-03  19:35:11.0000000    2007-07-30  2       1
12  100006043@N02   203         2013-08-05  14:02:49.0000000    2013-07-29  1       1
13  100006043@N02   1           2013-08-05  18:08:45.0000000    2013-07-29  2       2
14  100010324@N02   1           2013-04-14  19:19:13.0000000    2013-04-08  1       1
15  100010343@N03   367         2014-06-04  18:54:13.0000000    2014-06-02  1       1
16  100014982@N03   2909        2013-08-28  23:20:27.0000000    2013-08-26  1       1
17  100014982@N03   183         2013-09-13  12:26:41.0000000    2013-09-09  1       1
18  100018928@N06   1149        2013-08-01  08:40:22.0000000    2013-07-29  1       1
19  100018928@N06   1149        2013-08-01  08:40:23.0000000    2013-07-29  2       1
20  100018928@N06   1149        2013-08-01  08:40:32.0000000    2013-07-29  3       1
21  100018928@N06   1149        2013-08-01  08:40:33.0000000    2013-07-29  4       1
22  100021169@N02   10          2005-07-02  18:17:26.0000000    2005-06-27  1       1
23  100021169@N02   10          2005-07-02  18:18:13.0000000    2005-06-27  2       1