SQL - Identify the erroneous statement

Given below are 5 statements, which is to be executed after creation of the #tmp_test_table table. Identify the statement(s) which execute successfully or give error.

-- DROP TABLE --

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID = OBJECT_ID('tempdb..#tmp_test_table'))

BEGIN

DROP TABLE #Table

END

GO

-- CREATE TABLE --

CREATE TABLE #tmp_test_table (OrderNo INT, City VARCHAR(100), Country VARCHAR(100))

GO

INSERT INTO #tmp_test_table

SELECT 101, 'Reims', 'France' UNION ALL

SELECT 102, 'Delhi', 'India' UNION ALL

SELECT 103, 'Münster', 'Germany' UNION ALL

SELECT 104, 'Lyon', 'France' UNION ALL

SELECT 105, 'Reims', 'France' UNION ALL

SELECT 106, 'Delhi', 'India' UNION ALL

SELECT 107, 'Münster', 'Germany' UNION ALL

SELECT 108, 'Lyon', 'France'

GO

-- STATEMENT 1 --

SELECT * FROM #tmp_test_table

GO

-- STATEMENT 2 --

SELECT DISTINCT City, Country FROM #tmp_test_table ORDER BY CITY, COUNTRY

GO

-- STATEMENT 3 --

SELECT City, Country FROM #tmp_test_table ORDER BY CITY, COUNTRY

GO

-- STATEMENT 4 --

SELECT City, Country FROM #tmp_test_table ORDER BY CITY, COUNTRY, OrderNo

GO

-- STATEMENT 5 --

SELECT DISTINCT City, Country FROM #tmp_test_table ORDER BY CITY, COUNTRY, OrderNo

GO

-- DROP TABLE --

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE OBject_ID = OBJECT_ID('tempdb..#tmp_test_table'))

BEGIN

DROP TABLE #Table

END

GO

Answer: Successful, Successful, Successful, Successful, Error

Explanation: If you use DISTINCT clause, then Column names specified in the ORDER BY clause, must be defined in the select list.

Error Message: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.