Geek asks and answers

Sort Nulls last

You want to sort Nulls and Blanks last and read all the solutions that use either the Coalesce or If functions in column order.

My problems are not for me, because the column that I am sorting is dynamically set by the PHP script that creates the query, and some of my columns are in two tables in my join.

   $sortcol="boat";
   $sql= "SELECT fleet,b.boat as boat,owner FROM boats as b 
   LEFT JOIN owners as o ON  b.boat=o.boat 
   ORDER BY $sortcol";

This works fine, I can change the $ sortcol variable, and my output pin works fine except for the zeros and spaces at the top.

Based on other posts, I tried this

   $sortcol="boat";
   $sql= "SELECT fleet,b.boat as boat,owner FROM boats as b 
   LEFT JOIN owners as o ON  b.boat=o.boat 
   ORDER BY IF($sortcol is NULL,1,0), $sortcol";

. "- ORDER BY ". , b.boat , , , . , , orderby, .

.

+5
3

. - , MySQL ORDER BY, , , - ( . , ).

, .

( ):

select b.id, a.name as name
    FROM client AS a JOIN client AS b ON (a.id = b.id)
    ORDER BY name, name;

while COALESCE(name, ''), name IS NULL, name OR NULL .

, .

- :

SELECT * FROM ( your query here, without ORDER ) AS original
ORDER BY IF($sortcol is NULL,1,0), $sortcol;

:

$sortcol="boat";
$sql = <<<SQL
   SELECT * FROM (
      SELECT fleet,b.boat as boat,owner FROM boats as b 
         LEFT JOIN owners as o ON  b.boat=o.boat 
   ) AS original
   ORDER BY IF($sortcol is NULL,1,0), $sortcol;
SQL;
+4

ORDER BY, . .

SELECT, boat,

$sortcol = "bboat";
$sql= "
  SELECT 
    fleet,
    /* Alias as bboat to disambiguate from b.boat and o.boat
    b.boat as bboat,
    owner 
  FROM 
    boats as b
    LEFT JOIN owners as o ON  b.boat=o.boat 
  ORDER BY IF($sortcol is NULL,1,0), $sortcol";

: $sortcol , , SQL-.

+1

ANSI SQL, ORDER BY, - .

Therefore, when nameused in list order and not in an expression, it is interpreted as a reference to an alias. When used in an expression, it cannot resolve an alias, so MySQL is trying to find a column to resolve this. There are two such columns in your query, hence the error you get about the ambiguity.

This permission behavior can be seen from the following (tested in MySQL and SQL Server)

CREATE TABLE T
(
C INT,
D INT
)

INSERT INTO T
SELECT 1, 2 UNION ALL
SELECT 2, 1

SELECT C AS D, D AS C
FROM T 
ORDER BY D

Returns (sorted by alias)

D           C
----------- -----------
1           2
2           1

SELECT C AS D, D AS C
FROM T 
ORDER BY D + 0

Returns (ordered by base column)

D           C
----------- -----------
2           1
1           2
+1
source

More articles:


All Articles