SQL Server – logical question – Get rank from IDs

I’m trying to write a query to solve a logical problem using Redshift POSTGRES 8.

Input column is a bunch of IDs and Order IDs and desired output is basically a rank of the ID as you can see in the screenshot. (I’m sorry I’m not allowed to embed images into my StackOverflow posts yet)

If you could help me answer this question using SQL, that would be great! Thanks

Data

order id	id	size	desired output
1	abcd	2	1
1	abcd	2	1
1	efgh	5	2
1	efgh	5	2
1	efgh	5	2
1	efgh	5	2
2	aa	2	1
2	aa	2	1
2	bb	2	2
2	bb	2	2

Answer

SELECT
  *,
  DENSE_RANK() OVER (PARTITION BY order_item_id ORDER BY id)   AS desired_result
FROM
  your_table

DENSE_RANK() creates sequences starting from 1 according to the ORDER BY.

Any rows with the same ID will get the same value, and where RANK() would skip values in the event of ties DENSE_RANK() does not.

The PARTITION BY allows new sequences to be created for each different order_item_id.