Clever Geek Handbook

How to order an SQL query with grouped rows

I have a table (Product_Id, category priority, atribute1, atribute2 ...) in MS Access and I am trying to make a query that orders the data, grouped by categories and ordered by highest priority. The priority can be zero, 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

Expected result in request:

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
0


source to share


5 answers


In Jet SQL, this might work:



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);
+3


source


The simplest 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
+1


source


You need to weight the categories (I weight zero with some suitable 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)
0


source


As far as I know, Jet always sorts NULL at the end of the result set when an explicit ORDER BY is used.

See: ADO Provider Properties and Settings

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

The fact that the NULL Collation Order property is read-only, the vendor strongly suggests that the Jet engine has only one set of NULL values, and luckily for you, this is the one you want.

-1


source


Obviously NZ (Value, ValueToReturnIfNull) can be used on MSAccess as a replacement 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)
-1


source






More articles:


All Articles