CODEWITHSUNDEEP
mysqlsql
Filipek Pavel
Filipek Pavel

Reputation: 13

Two queries returned different result

I have a problem with SQL queries. I have two nearly identical queries that differ just that the first one returns only one column, id, and the second one returns all columns, *. Queries are as follows:

Query 1:

SELECT id 
FROM `import_data` 
WHERE 1 AND parent IS NULL 
ORDER BY CONCAT(category_1, COALESCE(category_2, ""), COALESCE(category_3, "")) DESC;

Query 2:

SELECT * 
FROM `import_data` 
WHERE 1 AND parent IS NULL 
ORDER BY CONCAT(category_1, COALESCE(category_2, ""), COALESCE(category_3, "")) DESC;

Each query has a different order of results, even though I have specified the same ORDER BY.

Upvotes: 1

Views: 74

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You would get different orders if you have multiple rows where CONCAT(category_1, COALESCE(category_2, ""), COALESCE(category_3, "")) are the same.

How does SQL order rows where the key values are the same? The answer is that the ordering is arbitrary and can change from one query to the next. SQL does not guarantee stable sorts, meaning that the order of the final sort, when there are ties, is indeterminate.

I would suggest that you just add id to the order by clause:

ORDER BY CONCAT(category_1, COALESCE(category_2, ""), COALESCE(category_3, "")) DESC, id

If id is unique, then the two queries will return the same order.

Upvotes: 4

Related Questions