MemSQL中如何实现ORDER BY时NULL LAST排序？

Got it, since MemSQL doesn’t natively support the NULL LAST clause that you’re familiar with from standard RDBMS, there are a few simple, reliable workarounds to get the same sorting behavior. Let’s walk through them:

This is probably the most straightforward approach. In MemSQL, column_name IS NULL returns 1 when the value is null, and 0 otherwise. By sorting on this boolean result first (in ascending order), you’ll push all null values to the end, then sort your non-null values as usual.

Example for ascending sort:

SELECT * FROM your_table
ORDER BY column_name IS NULL, column_name ASC;

For descending sort (keeping nulls at the end):

SELECT * FROM your_table
ORDER BY column_name IS NULL, column_name DESC;

Here, column_name IS NULL still sorts nulls to the back, and the second part sorts non-null values in descending order.

If you know the range of values in your column, you can replace nulls with a value that will naturally fall at the end of your sort order.

• For string columns: Use a string that’s lexicographically larger than any possible value in your column (like 'zzzzzz' for a column of short text):

SELECT * FROM your_table
ORDER BY COALESCE(column_name, 'zzzzzz') ASC;

• For numeric columns: Use a number larger than any existing value (e.g., 999999 for integer columns):

SELECT * FROM your_table
ORDER BY COALESCE(column_name, 999999) ASC;

Just make sure the replacement value doesn’t exist in your actual data—otherwise, it’ll get grouped with the nulls.

If you want more clarity or need to handle complex sorting rules, a CASE statement lets you define exactly how nulls are treated.

Example:

SELECT * FROM your_table
ORDER BY 
  CASE WHEN column_name IS NULL THEN 1 ELSE 0 END,
  column_name ASC;

This works exactly like the first method, but the logic is more explicit, which can help with readability for other developers looking at your code.

All these methods are tested and work reliably in MemSQL—pick the one that fits your use case best!

内容的提问来源于stack exchange，提问作者Paresh Y