How to get certain SQL query results at the top of the list

Question

I'm convinced this will make me slap myself when I see the answer but here goes...

Say I need a list of all products from a certain vendor but they need to order by a specific variable productType at the top and below it doesn't really matter but all products have to be in the list.

So basically

SELECT * FROM Products p WHERE p.VendorID = 1 AND p.ProductType = 'widget'

as the first rows to display. Then,

SELECT * FROM Products p WHERE p.VendorID = 1 AND p.ProductType <> 'widget' 

underneath that.

I'm using LINQ if that helps any but I can't even get this in regular SQL queries

Answer 1

Create an extra field in the select part that indicates if they should be on top. For instance.

SELECT *, (p.ProductType = 'widget') AS thisfirst  FROM Products p WHERE p.VendorID = 1 ORDER BY thisfirst

Answer 2

Just order by a case statement like so:

select * from products
order by case producttype when 'widget' then 1 end nulls last;

Answer 3

One option is:

SELECT *, 
        CASE p.ProductType WHEN 'widget' THEN 0 ELSE 1 END AS ProductPriority 
    FROM Products p 
    WHERE p.VendorID = 1 
    ORDER BY ProductPriority

Answer 4

I guess you're using LINQ to SQL, so I think it would be better to do this using a LINQ query:

db.Products
  .Where(p => p.VendorID == 1)
  .OrderBy(p => p.ProductType == 'widget')

Answer 5

In Sql:

SELECT *
FROM Products p
WHERE p.VendorID = 1
ORDER BY CASE WHEN p.ProductType = 'widget' THEN 1 ElSE 2 END

And in Linq:

IQueryable<Product> query =
  from p in dc.Products
  where p.VendorID == 1
  orderby p.ProductType == "widget" ? 1 : 2
  select p;

Answer 6

An alternate approach to Brian's would be a union query:

Select ...
From    (
        Select ..., 0 As SortOrder
        from Products
        Where VendorId = 1
            And ProductType = 'Widget'
        Union All 
        Select ..., 1
        from Products
        Where VendorId = 1
            And ProductType <> 'Widget'
        ) As Z
Order By Z.SortOrder