tsql icon indicating copy to clipboard operation
tsql copied to clipboard

Investigate SQLite ORDER BY outer query column not allowed

Open AnyhowStep opened this issue 5 years ago • 1 comments
trafficstars

This is invalid in SQLite,

CREATE TABLE T (
  id INT
);
CREATE TABLE U (
  id INT PRIMARY KEY,
  other INT
);
INSERT INTO T VALUES 
	(1),(2),(3);
INSERT INTO U VALUES 
	(4, 7),(5, 8),(6, 9);

SELECT
	*
FROM
	T
WHERE
	EXISTS (
    	SELECT
      		*
      	FROM
      		U
      	GROUP BY
      		U.id
      	ORDER BY
      		T.id ASC
    )

You cannot ORDER BY an outer query column. Why wouldn't SQLite allow this?

AnyhowStep avatar Jan 28 '20 04:01 AnyhowStep

The above is a contrived example. But there may be valid use cases. Like not using EXISTS() and using stuff like U.other / T.other

AnyhowStep avatar Jan 28 '20 05:01 AnyhowStep