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.

No comments:

Post a Comment