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

Fionnuala · Accepted Answer · 2008-12-22T13:36:28.080

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

edited Dec 22 '08 at 13:36

answered Dec 22 '08 at 13:16

Fionnuala

90,370
7
114
152

bah you just beat me :) Question your 'table' in the inner join is significantly more complicated than the example I used. I'm not sure it needs to be. – Ron Tuffin Dec 22 '08 at 13:23
I was working with ZLS not Null fields from cut-and-paste :( . I have corrected it. – Fionnuala Dec 22 '08 at 13:35
"In Jet SQL, this may suit" - no, because Jet SQL does not support the NZ() function. You should have said, "In MS Access..." or similar because the NZ() function is provided by the MS Access interface. In Jet outside of the MS Access interface your SQL errors: Undefined function 'NZ' in expression. – onedaywhen Jan 05 '09 at 08:54
That is true. "Jet used with Access " perhaps? – Fionnuala Jan 07 '09 at 00:07
Better is IIF([Field3] IS NULL, 999, [Field3]) because it will work in both Jet and MS Access and avoids known issues with data typing (which affects sorting) with NZ(). For other reasons, see http://allenbrowne.com/QueryPerfIssue.html. – onedaywhen Jan 08 '09 at 11:02

score 1 · Answer 2 · edited Sep 12 '17 at 13:10

1

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

edited Sep 12 '17 at 13:10

C B

1,677
6
18
20

answered Jan 05 '09 at 06:43

Einstein

4,450
1
23
20

score 0 · Answer 3 · answered Dec 22 '08 at 11:17

0

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)

answered Dec 22 '08 at 11:17

Unsliced

10,404
8
51
81

MS Jet won't understand this. – Tomalak Dec 22 '08 at 11:19
Actually you need "order by t2.weight desc" and use "isnull(t1.priority,0)" and max() - or how I understand original question :) – Arvo Dec 22 '08 at 11:37
Jet doesn't support COALESCE or ISNULL. – onedaywhen Dec 22 '08 at 12:08
ISNULL can be replaced with Nz() in Jet SQL, no? I don't believe there's any command to COALESCE in Jet SQL. – David-W-Fenton Dec 23 '08 at 03:45
Actually, no, NZ() is not native Jet SQL syntax. NZ() is provided by the MS Access object model and therefore only available in queries via the MS Access interface. The native Jet is to use IIF, which FWIW Allen Browne reckons is superior to NZ() anyhow (http://allenbrowne.com/QueryPerfIssue.html) – onedaywhen Jan 02 '09 at 14:21

score -1 · Answer 4 · answered Dec 22 '08 at 12:18

-1

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.

answered Dec 22 '08 at 12:18

onedaywhen

55,269
12
100
138

If "null calues are sorted at the low end of the list" then teh nulls will appear first on the list. this is contrary to the question where null "should be placed at the end". – Ron Tuffin Dec 22 '08 at 13:13
Ron, you seem to have misinterpreted the meaning of "low end of the list". The key word here is "end". Think of a recordset: the NULLs will be sorted to the positions nearest the EOF (*end* of file). Did you test it with Jet? Try it and I think you'll agree that the NULLs do not appear first. – onedaywhen Jan 02 '09 at 13:52

score -1 · Answer 5 · edited May 23 '17 at 10:33

-1

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)

edited May 23 '17 at 10:33

Community

1
1

answered Dec 22 '08 at 13:19

Ron Tuffin

53,859
24
66
78

Thanks a lot Ron, but it does not work completely yet. It works perfectly if there are Priorities defined. As soon as there is more than one Category without Priority then these Categories are not grouped. – Dec 22 '08 at 13:34
I'm not sure I follow. The above should result in any category that has only a NULL priority sink to the bottom. If you want to sort these categories (the ones with only NULL as priorities) amongst themselves then add a a.category to the end of the ORDER BY clause. Let me know, I will then update. – Ron Tuffin Dec 22 '08 at 14:09
thanks again Ron, that was it, I added the a.category after b.min_priority_in_cat and works perfect. I found it out from Remou (tn.Field2). Thanks for your help! – Dec 22 '08 at 14:14
"NZ(Value, ValueToReturnIfNull) can be used on jet as a substitute for ISNULL" - Ron, Jet does not have an NZ() function. MS Access does, though. – onedaywhen Jan 02 '09 at 13:54

How to Order a SQL Query with grouped rows

5 Answers5