How to Order a SQL Query with grouped rows

Question

I have the table (Product_Id, category priority, atribute1, atribute2...) in MS Access, and I am trying to make a query that orders the data grouped by Category and ordered by the highest priority. Priority can be Null, so it should be placed at the end. Example: Table

1, 100, 2, atr1, atr2
2, 300,  , atr1, atr2
3, 100, 5, atr1, atr2
4, 200, 9, atr1, atr2
5, 100,  , atr1, atr2
6, 200, 1, atr1, atr2

Result expected in Query:

6, 200, 1, atr1, atr2
4, 200, 9, atr1, atr2
1, 100, 2, atr1, atr2
3, 100, 5, atr1, atr2
5, 100,  , atr1, atr2
2, 300,  , atr1, atr2

Answer 1

You need to weight the categories (I'm weighting null with some suitably large value):

select  t1.* 
from    myTable t1 
join 
( 

    select  category, min(coalesce(priority, 1000)) weight
    from    myTable 
    group by category
) t2 
on t1. category = t2. category
order by t2.weight, coalesce(t1. priority, 1000)

Answer 2

As far as I know, Jet always sorts NULLs to the end of a resultset when an explicit ORDER BY is used.

See: ADO Provider Properties and Settings

"NULL Collation Order: A Long value (read-only) that specifies where Null values are collated (sorted). For the Microsoft Jet provider, the value is always 4, which indicates that null values are sorted at the low end of the list."

The fact the property NULL Collation Order is read-only for the provider strongly suggests the Jet engine has only one NULL collation and, happily for you, it's the one you desire.

Answer 3

In Jet SQL, this may suit:

SELECT t2.MinOfPriority, tn.Field2, Nz([tn.Field3],999) AS Priority, 
       tn.Field4, tn.Field5
FROM tn 
INNER JOIN (SELECT Min(Nz(tn.Field3,999)) AS MinOfPriority, tn.Field2
            FROM  tn GROUP BY tn.Field2) AS t2 ON tn.Field2 = t2.Field2
ORDER BY t2.MinOfPriority, tn.Field2, Nz([Field3],999);

Answer 4

Apparently NZ(Value, ValueToReturnIfNull) can be used on MSAccess as a substitute for ISNULL so ...

SELECT a.*
FROM this_table AS a 
    INNER JOIN 
    (
        SELECT category,min(NZ(priority,999999)) as min_priority_in_cat 
        FROM this_table group by category
    ) AS b ON a.category = b.category
ORDER BY  b.min_priority_in_cat, a.category, NZ(a.priority,999999)

Answer 5

The easiest solution (not necessarily the best in some cases) is to use column numbers in your ordering expressions:

SELECT t2.MinOfPriority, tn.Field2, Nz([tn.Field3],999) AS Priority, tn.Field4, tn.Field5

ORDER BY 1,2,3