NULL sort order

[Shab]

Hi All,

I have a table with 3 columns. Product, Location and Value. The data looks like this:

NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40

This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:


Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100

Thanks,

Shab

 

[JohnDTampaBay]

I think this will work. Good luck!

SELECT Product, Location, Value
FROM YourTable
ORDER BY IsNull(Product,'99999')
    , IsNull(Location, '99999')

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[Shab]

Well...
This does not work. Plus even if it worked it would put all the NULLs at the end. I need them exactly where they are located. Remember they are subtotal lines and they need to be at the end of each section.

Shab

 

[johnherman]

Following JohnDTampaBay, How about order by Location first, then Product.

-------------------------
Sometimes the grass is greener on the other side because there is more manure there !

 

[Shab]

No, sorting by location didn't work either. But I made a small change and it works now.


SELECT [Location], [Product], Value
from
##MyTable
ORDER BY isnull([Location],'zzz') , isnull([Product] ,'zzz')

Since the data is characters, 9999 will always get sorted first, whereas zzzzz gets sorted last.


Thanks,

Shab