none
How Null value are sorted in datatable.select(filter,sort) RRS feed

  • Question

  •  

    Hi,

    I would like to know how null value in a column of a datatable are sorted when I do a select with a sort parameter.

    From several tests I made it seems like ASC implies NULL FIRST and DESC implies NULL LAST.

    Can I take that for granted? Is it possible to force this beheviour.

     

    Thanks.

    Monday, February 18, 2008 8:19 AM

Answers

  • Yes, you can expect the NULL value to be on top of sorting in a case of ASC and at the bottom in a case of DESC.

     

     

    Wednesday, February 20, 2008 10:25 AM
    Moderator

All replies

  • If you want total control of the sort order of your rows you should try using ANSI SQL ORDERBY clause which gives predictable results because it is a cursor by definition.  Try the link below for the T-SQL ORDERBY for SQL Server.

     

    http://msdn2.microsoft.com/en-us/library/ms188723.aspx

    Monday, February 18, 2008 7:40 PM
  • I believe records are sorted properly if they contain NULL values. So you should expect that NULL values will be first in a case of ASC and last in a case of DESC.

     

    Tuesday, February 19, 2008 10:51 AM
    Moderator
  • There's no explicit ordering of NULLs in the SQL standard.  Consequently, different databases do it differently.

    Tuesday, February 19, 2008 8:11 PM
  • Null is not relevant to ORDERBY in ANSI SQL because it is a cursor what is relevant is collation BIN(binary sort) is the fastest.  But it require case sensitive data in column, table or database depending on where the BIN collation is set. 

    Tuesday, February 19, 2008 8:41 PM
  •  

    My Question is about the select method of a DataTable Object in the System.Data namespace. Sorry If' m not in the correct forum

    I was wondering if I want to loop on an array of rows given by a select method:

    DataRow[] tab = DataTable.Select("","COLUMN ASC");

    foreach(DataRow dr in tab)

    ....

    I can expect the Null Value in Column to be first or, to be sure, I Have to loop on 2 tabs:

    DataRow[] tab1 = DataTable.Select("COLUMN IS NULL");

    then

    DataRow[] tab2 = DataTable.Select("COLUMN IS NOT NULL",'"COLUMN ASC");

    Wednesday, February 20, 2008 7:11 AM
  • Yes, you can expect the NULL value to be on top of sorting in a case of ASC and at the bottom in a case of DESC.

     

     

    Wednesday, February 20, 2008 10:25 AM
    Moderator