Time for this week’s puzzle/teaser. I want to return the results in ascending order but without using ORDER BY
If you run this code
CREATE TABLE Puzzle
(Col1 varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
Col2 varchar(20) NOT NULL UNIQUE NONCLUSTERED);
INSERT INTO Puzzle (Col1, Col2)
SELECT 'Z', 'AA'
UNION ALL
SELECT 'A', 'BB'
UNION ALL
SELECT 'B', 'CC'
UNION ALL
SELECT 'C', 'DD'
UNION ALL
SELECT 'M', 'EE';
SELECT Col1 FROM Puzzle;
DROP TABLE Puzzle;
you get these results
Col1
Z
A
B
C
M
What I want to see is the following
Col1
A
B
C
M
Z
Without using ORDER BY, how would you make the SELECT query return Col1 in ascending order? You can’t make changes to the table, all that you are allowed to modify is this part
SELECT Col1 FROM Puzzle;
Warning
Do not use these suggestion in production code, the only real way to guarantee order of a result set is by using ORDER BY

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.