7

I am asked a question in interview that sort result without using of order by clause and without using the script like php. I enough google it but did not find the way. Is there any method to sort results in this way. And it should be without using of any script like php etc.

Chuck Norris
  • 15,207
  • 15
  • 92
  • 123
nbhatti2001
  • 353
  • 2
  • 7
  • 33

3 Answers3

16

You cannot, at least not reliably.

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

There's a good chance that the order in which rows are returned may well depend on the insertion and deletion activity since the table was created.

My answer to an interview question like that would be:

Is there some sort of reason why we can't use 'order by' in our queries? Is the company so hard up for money that they cannot afford the disk space to store those extra few bytes for each query? Are you out of you goddamned mind? Ask me a question that's going to have some relevance :-)

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • 4
    +1: proper sentiment, but such phrasing isn't going to impress the typical interviewer. – wallyk Feb 20 '12 at 06:47
  • Well, if you really want that job, perhaps you should limit yourself to using paragraphs 1 through 3 above. Me, I'm old and cantankerous and I can get away with stuff like that :-) – paxdiablo Feb 20 '12 at 06:48
  • ok, so there is no way to sort it without order by clause mean an authentic way. He was just exploring my mind for different option then. – nbhatti2001 Feb 20 '12 at 10:11
  • I agree with your sentiment, too. That being said, understanding the default order a particular SQL implementation will return rows in was critical to explaining an errant behavior (caused by a missing `ORDER BY` clause, coincidentally enough) to a client in my realm today. Hence *my* Google search that brought me to *your* answer. – Bob Kaufman Mar 16 '16 at 20:34
  • From the guide of a product I support:' ' Using Advanced SQL Conditions in SBM Reports // ' ' SQL Conditions Limitations // ' ' **SBM Advanced SQL conditions does not support the use of the ORDER BY subquery**. ORDER BY with OVER() function may be a valid use case for your database; however, SBM does not currently support that condition. – mnemotronic Apr 28 '16 at 14:21
3

Create a 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 the rows

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 query will order the data as per 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
user2454241
  • 31
  • 1
3

I expect they were fishing to see if you knew that most SQL implementations in general do not guarantee the order of rows returned unless you explicitly use an order by clause.

My Other Me
  • 5,007
  • 6
  • 41
  • 48