How to get incremental rowid by column value in SQL-CodePudding

PostgreSQL

I have this query:

SELECT *
FROM 
    (SELECT 
         (CASE
             WHEN "Review".food IS NOT NULL AND "Review".service IS NOT NULL
                 THEN ("Review".food   "Review".service) / 2
             ELSE COALESCE(service, COALESCE(food, NULL)) END) AS rating,
         "venueId" AS item_id,
         "userId" AS user_id
     FROM "Review") AS data
WHERE rating IS NOT NULL
  AND user_id IS NOT NULL
  AND item_id IS NOT NULL
ORDER BY item_id;

This returns an output like this:

But I need to have one additional column incremental_id which will contain id of the each row, bases on the item_id.

For example:

Where for items with:

 item_id = 2  incremental_id = 1;
 item_id = 4  incremental_id = 2;
 item_id = 8  incremental_id = 3;
 item_id = 20 incremental_id = 4;
 item_id = 23 incremental_id = 5;

How I need to change my query to get the desired output format?

CodePudding user response：

You can use dense_rank window function for this. Keep your query and just add one more expression after the *. Here it is:

SELECT *, dense_rank() over (order by item_id) as incremental_id
FROM -- ... the rest of your query as-it-was ...