Sort column without using ORDER BY

Transact-SQL
1

Hello Folks,

New bee here.

Today i had an interview with one of the social media sites. The only question they asked me was how do you sort a column without using ORDER BY clause?

Could someone please take a stab at this?

create table test_sort(id number, amt number);

insert into test_sort values(10, 100);
insert into test_sort values(20, 300);
insert into test_sort values(30, 50);
insert into test_sort values(40, 10);
insert into test_sort values(50, 400);

Expected output for AMT column:

10
50
100
300
400

Thanks!

2

There is NO method in SQL Server to get the output sorted in a specific order other than by specifying a ORDER BY clause in the final select. Any sort order that you may see in the absence of a order by clause cannot be relied upon.

From MSDN: The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

3

Thanks for the reply.

That the whole point of the question, we know the data is stored in heap and there is no guarantee that order of insert is same as order of retrieval . That is the reason why i gave the insert statements without an order. If database does not provide the utility how do we do it in a different way? The interviewer was probably testing approach to a given problem.

Self join? recursive SQL? Correlated sub query? case statements? combination of all or any of these? anything else?

4

If you put a clustered index on the AMT column the data will probably, especially under low loading, be returned in the order of the clustered index. This is not guaranteed and should not be relied upon.

By definition a relation (table) is an unordered set so the only way to guarantee the order of a SELECT statement is to use the ORDER BY clause.

5

I was told no INDEX should be used.

6

You don't!
As @JamesK mentioned, there are absolutely no guarantee, in which order the rows are returned (unless using "order by").

I have not tried the suggestion from @Ifor, but as he mentioned --> no guarantee. And you also mentioned, no index should be used.

The way you do the "create table" leads me to belive, you are not using Microsoft SQL Server (but I could be wrong). It looks like Oracle and maybe Oracle has some kind of guarantee regarding "not order by" that Microsoft doesn't (but I doubt it).

Now, I couldn't help but try to come up with some sort of solution, as this request is "just to stupid or tricky".
My attempts:
option 1) have the frontend program do the sorting, thus "order by" is not used.

option 2) "stuff" all data in at declared string, using while loop counting from min value to max value. Split it with "string_split" (which I belive splits in the order from left to right. I'm not sure if this is guaranteed, but I'm pretty sure).

create table test_sort(id int, amt int);

insert into test_sort values(10,100);
insert into test_sort values(20,300);
insert into test_sort values(30,50);
insert into test_sort values(40,10);
insert into test_sort values(50,400);

declare @i1 int=0;
declare @i2 int=0;
declare @txt varchar(max)='';
select @i1=min(amt),@i2=max(amt) from test_sort;

while(@i1<=@i2)
begin
   select @txt+='¤'+cast(id as varchar(10))+'§'+cast(amt as varchar(10))
     from test_sort
    where amt=@i1
   ;
   set @i1+=1;
end;

select left(value,charindex('§',value)-1) as id
      ,right(value,len(value)-charindex('§',value)) as amt
  from string_split(right(@txt,len(@txt)-1),'¤')
;

drop table test_sort;

Lastly I have to say: Don't EVER use this method, as it's a performance killer and has the limit of the max size of varchar.

7

They told me the solution is DB vendor agnostic. They did not even give me a table name or structure. It could be character column or numeric column. I was not supposed to use any DB vendor specific functions or language. So, PL/SQL, T-SQL, indexing schematics etc are out of question.

They were expecting a ANSI SQL solution. Similar to how we can rewrite a "normal" SQL for GROUPING SETS or any ANALYTICAL function.

This is the only question I was asked and needless to say i was disqualified :slight_smile:

8

I don't know if other vendors have some way of getting a result set ordered in a predictable way without using an order by clause, but Microsoft SQL Sever certainly does not. Since Microsoft is a major vendor, I would surmise that any answer you give should work for Microsoft SQL Server as well. If that is the case, and if you told them that there is no way to get a predictable sort order without using order by clause, you would be 100% correct.

These type of tricky questions, and asking only one question and making a decision based on that is really disrespectful to you and your time. I don't know why interviewers do that. My only conjecture is that they are insecure on unsure of themselves, and just want to show off. In my opinion, an interview has to be a conversation, respectful on both sides, so each can determine whether to enter into a business relationship and how much value the potential employee can add.

1 Like
9

Got to thinking about "ansi sql" and came up with this:

select id
      ,amt
  from test_sort
 group by amt
         ,id
;

It sorts correctly, but I still belive, we still have no guarantee that it will sort correct every time.

10

I agree with you James. I would not have taken the position even if they would have offered me. You are right it is an insult to the candidate. Had I been on the other side, i could ask the interviewer 10 different gotcha questions and make him/her look stupid. You are right, they are insecure.

Bitsmed,

I gave group by as one of the solution and gave a disclaimer that sort is not always guaranteed. It was not the answer they were looking for.

11

Pretty sure this is impossible. ANSI SQL explicitly states you can never guarantee the order of results unless there is an ORDER BY clause. No matter how many hoops you jump through, at some point you have to have a final SELECT that puts the results into a result set and if that has no ORDER on it, then the order of result isn't guaranteed.

Some of the other suggestions, like string_split or a clustered index will probably appear to work in simple cases, but it isn't guaranteed. Unless the answer was expected to be "You sort the data in code after it comes out of the database", but honestly that's probably a sign it's a company you don't want to be working at....