Is this the same as LIMIT without ORDER BY?

No. That one has no sort, so the rows change between runs. Here the sort is stable and deterministic. NULL just lands at the front of a descending order in Postgres.

Which databases put NULL first?

Postgres sorts NULL as larger than every value, so ASC puts it last and DESC puts it first. MySQL and SQL Server treat NULL as smaller than every value, so ASC puts it first and DESC puts it last. Supabase and Neon are Postgres.

Does the ASC case cause trouble too?

Yes, the other way round. 'Customers who haven't logged in for the longest' sorted by last_login ASC returns never-logged-in customers first in MySQL and SQL Server, and last in Postgres. The same query answers differently by database.

Why does the AI's answer still look plausible?

If the query selects only the name, nothing in the result shows the missing value. The model reads ten real customer names as the top ten.

What is the safest fix?

Filter first when NULL means no data: WHERE value IS NOT NULL. If you need to keep those rows, sort them to the end explicitly instead of relying on the default.

ORDER BY DESC NULLS FIRST | Top 10 Blank

You ask: "Who are our top 10 customers by lifetime value?" The AI lists ten names. None has a figure next to it, because it only selected the name. You have thousands of customers, and you've never heard of these ten.

In Postgres, ORDER BY lifetime_value DESC puts NULL first.

Where NULL sorts depends on the database

-- Postgres: NULL rows come before every real value
SELECT name FROM customers
ORDER BY lifetime_value DESC
LIMIT 10

| Database | ASC | DESC | |---|---|---| | Postgres (including Supabase, Neon) | NULL last | NULL first | | MySQL | NULL first | NULL last | | SQL Server | NULL first | NULL last |

A customer with no purchases yet has a NULL lifetime value. In Postgres that customer outranks everyone. The first ten rows are ten of them, so the "top ten" is whichever ten happened to come first.

Nothing errors and the rows look real. The mirror image hurts too: sort last_login ASC to find the customers inactive longest. In MySQL, never-logged-in customers come first. In Postgres they come last. The same SQL gives a different list. This is the filter that skips NULL problem, moved to the sort order.

The fix

-- Postgres
ORDER BY lifetime_value DESC NULLS LAST, id

-- MySQL (no NULLS LAST syntax)
ORDER BY lifetime_value IS NULL, lifetime_value DESC, id

-- SQL Server (also works in Postgres)
ORDER BY CASE WHEN lifetime_value IS NULL THEN 1 ELSE 0 END,
         lifetime_value DESC, id

-- or just drop them, when NULL means no data
WHERE lifetime_value IS NOT NULL

The trailing id breaks ties, so the result is stable between runs. If lifetime value is a sum over orders, the empty ones may also be NULL instead of zero.

How to catch it

SELECT COUNT(*) - COUNT(lifetime_value) AS null_count FROM customers
-- if this is above 0, the sort order for NULL matters
-- and always select the sort column so a blank is visible

If the AI shows a ranking, ask it to show the value it ranked by.

The prompt that heads this off

Whenever you sort and limit (top N, bottom N, oldest, newest), select the
sort column, add a tie-breaker, and say what you did with NULL values.
Exclude NULL unless I ask for them.

The short version

DESC does not mean "biggest first" when there are NULLs. Postgres puts them at the front, MySQL and SQL Server at the back. Select the column you sort by, and decide what happens to the empty ones.

Read-only MCP gateway: mcpserver.design.

Related reading: