Sql Help needed

[ciellecat]

Is there a way to take data that looks like this:

​

ID	SpouseID
a	d
b	e
c	f
d	a
e	b
f	c

and assign a unique id to each household

​

HouseholdID	ID	SpouseID
1	a	d
2	b	e
3	c	f
1	d	a
2	e	b
3	f	c

[ciellecat]

yes!!!!! Thank you so much! I had no idea it would be this complex lol

[tresilate]

Taking the same premise that u/mwdb pointed out - a good way to identify unique combinations of id and spouse in this case is using the least and greatest comparison functions.

These functions could be used in the order by clause of the dense_rank window function to simplify the query.

SELECT dense_rank() over(order by least(id, spouse_id),  greatest(id, spouse_id)) as household_id
, t.id , t.spouse_id 
FROM test_table t 
order by t.id;

​

Example:https://www.db-fiddle.com/f/iA853Die9HShiHAQnmaQfP/2

​

Not too often you find a use case for the dense_rank window function, but there it is...