Sorting of NULL Values






Sorting of NULL Values

NULL values introduce a problem with sorting, and the various SQL products handle the ordering of NULL values in different ways. You should consult the relevant SQL manuals for more details. Four options exist:

  • NULL values are always presented first, regardless of whether the ordering is ascending or descending.

  • NULL values are always presented last, regardless of whether the ordering is ascending or descending.

  • NULL values are seen as the lowest values.

  • NULL values are seen as the highest values.

MySQL treats NULL values as the lowest values in a column. Therefore, they are always placed at the bottom of the result if the order is descending and at the top if the order is ascending; see the following example and the accompanying result.

11. Get the different league numbers, and sort the result in descending order.

SELECT   DISTINCT LEAGUENO
FROM     PLAYERS
ORDER BY 1 DESC

The result is:

LEAGUENO
--------
8467
7060
6524
6409
2983
2513
2411
1608
1319
1124
?

Exercise 12.1:

Show at least three different ORDER BY clauses that would sort the PLAYERS table in ascending order by player number.

Exercise 12.2:

Indicate which of the following SELECT statements are incorrect:

  1. SELECT   *
    FROM     PLAYERS
    ORDER BY 2
    

  2. SELECT   *
    FROM     PLAYERS
    ORDER BY 20 DESC
    

  3. SELECT   PLAYERNO, NAME, INITIALS
    FROM     PLAYERS
    ORDER BY 2, INITIALS DESC, 3 ASC
    

  4. SELECT   *
    FROM     PLAYERS
    ORDER BY 1, PLAYERNO DESC
    

Exercise 12.3:

For each match, get the player number, the team number, and the difference between the number of sets won and the number of sets lost; order the result in ascending order on this difference.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows