CODEWITHSUNDEEP
sqlsql-serverdatabase
user2463808
user2463808

Reputation: 179

sort a column with duplicate values by another column in sql

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions