Filtering/ORDER BY – Practice

I ran into a small issue with a order I am trying to do and not sure if there is a solution to what I am trying to accomplish. I have the following table created… CustomerID is PK and I can’t change the numbers around.

Customer ID     ProductID     State
1   100 Alaska
2   100 California
3   100 North Carolina
4   100 South Carolina
5   101 Alabama
6   101 Virginia
7   101 Michigan
8   103 Hawaii
9   103 Kansas
10  103 Montana

The goal is to get the table to look like this:

1   100 Alaska
2   100 California
11  100 Delaware
3   100 North Carolina
4   100 South Carolina
5   101 Alabama
6   101 Virginia
7   101 Michigan
8   103 Hawaii
9   103 Kansas
10  103 Montana

The first part of what I’m trying to accomplish is adding the following record/row/entry (seen here)

11  100 Delaware

I was successfully able to add the row using INSERT INTO. The next task I wanted to accomplish was sorting (ORDER BY) the table by ProductID AND State. As you can see I am trying to move that new row to the new position as seen above.

As of now I have tried:

ORDER BY ProductID, State ASC;

My table looks like this

1   100 Alaska
2   100 California
11  100 Delaware
3   100 North Carolina
4   100 South Carolina
5   101 Alabama
7   101 Michigan
6   101 Virginia
8   103 Hawaii
9   103 Kansas
10  103 Montana

This changes the order of customer ID 5,6,7 which I am trying to avoid.

I tried to create a CASE but had no luck and wasn’t able to “double sort” (error because my first 2 columns are int and can’t compare to my 3rd column)

Any recommendations? Advice? Is what I’m trying to accomplish even possible?

Answer

Probably this is what you are after?

select * 
from t
order by productId, case when productId=100 then state end,
customerId

Advertisement

Answer