Sort results without using order by condition

Question

I am asked in an interview that the sort result is not used when using the order by clause without using a script like php. I google enough, but haven't found a way. Is there a way to sort the results this way. And it should be without using any script like php etc.

Answer 1

You cannot, at least not reliably.

Some SQL implementations may return rows in the order of their primary keys or clustered indexes, but SQL itself is a relational algebra that returns randomly ordered sets, unless specifically indicated otherwise.

It is very likely that the order in which rows are returned can be highly dependent on the insert and delete activity since the table was created.

My answer to this interview question:

Is there any reason why we cannot use "order" in our queries? Has the company really made so much money that they cannot afford the disk space to store these extra bytes for each request? Are you damned of you? Ask me a question that will make some sense :-)

Answer 2

I expect them to fish if you know that most SQL implementations do not guarantee the order of returned rows at all unless you explicitly use the order by clause.

Answer 3

Create table

USE [Test] GO /****** Object: Table [dbo].[Test_order] Script Date: 06/05/2013 10:21:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Test_order]( [TID] [int] IDENTITY(1,1) NOT NULL, [RID] [int] NULL, [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF 

then insert lines

 insert into dbo.Test_order values(1,'Test1') insert into dbo.Test_order values(3,'Test3') insert into dbo.Test_order values(2,'Test2') insert into dbo.Test_order values(5,'Test5') insert into dbo.Test_order values(6,'Test6') insert into dbo.Test_order values(4,'Test4') insert into dbo.Test_order values(9,'Test9') insert into dbo.Test_order values(7,'Test7') insert into dbo.Test_order values(8,'Test8') 

this request will order data according to RID

 select distinct b.TID,b.RID,b.[Name] from dbo.Test_order as a,dbo.Test_order as b where a.RID>=b.RID