Reputation: 179
I have a following table:
+------+--------+
| Type |Location|
+------+--------+
| 0003 | US |
| 0010 | US |
| 0007 | CA |
| 0013 | MX |
| 0003 | EU |
| 0007 | MX |
| 0219 | CN |
+------+--------+
I need it sorted by "Location", except when a duplicate is found in the "Type" column. Then the next row should be duplicate (or more duplicates) before proceeding further with sorting by "Location".
Expected result:
+------+--------+
| Type |Location|
+------+--------+
| 0007 | CA |
| 0007 | MX | <---- Exception (duplicate of 0007)
| 0219 | CN |
| 0003 | EU |
| 0003 | US | <---- Exception (duplicate of 0003)
| 0013 | MX |
| 0010 | US |
+------+--------+
I've tried to play with cte and row_number but I couldn't come anywhere close. I am not sure if this was a good approach, but I can't think of any other solution.
Upvotes: 2
Views: 2008
Reputation: 1271241
You can do what you want with a window function in the order by
:
select t.*
from t
order by (min(location) over (partition by type)),
location
Upvotes: 5