SQL order by [Order] but group similar items together

Sorry if I don't really know the terms for this, I will try to explain this the best I can...

This is what I've got now

SELECT * FROM Products ORDER BY [Order]

 Order  ProductId  Color
   1       10      Black
   2       59      Black
   3       10      White
   4       48      Black

(the table is simplified, the real table has about 30,000 records)
But this is what I actually want

 Order  ProductId  Color
   1       10      Black
   3       10      White
   2       59      Black
   4       48      Black

So I want it sorted by [Order], but keep the same ProductId close together, so the 3rd line should move up next to the first, because their ProductId is the same (ie. 10)

Is that even doable?
If it's not possible in SQL Server query, is there any easy way to do it in LINQ perhaps?

Jon Skeet
people
quotationmark

It nearly sounds like you don't really want it sorted by order, primarily - you want it sorted by product ID and then order. So:

SELECT * FROM Products ORDER BY ProductId, [Order]

And in a LINQ query expression:

var results = from product in db.Products
              orderby product.ProductId, product.Order
              select product;

or:

var results = db.Products.OrderBy(p => p.ProductId).ThenBy(p => p.Order);

However, as noted in comments, that will give the wrong order for the last two lines.

If you're really after "use order as the primary key, but then hike any products in different orders within that group" then it's somewhat tricky. I'm not sure that what you're looking for is easily representable in SQL... ORDER BY really assumes you want your primary ordering to be a real primary ordering...

You could try this in LINQ - I don't know what the equivalent SQL would be, or whether it will actually work...

var results = db.Products
                .OrderBy(g => g.Order)
                .GroupBy(p => p.ProductId)
                .SelectMany(g => g);

In LINQ to Objects I believe that would work - but it's unclear whether it will in LINQ to SQL.

people

See more on this question at Stackoverflow