How to order the records

Question

How to order the records

kiran.rajenimbalkar

SSCrazy

Points: 2603
More actions
December 7, 2015 at 4:01 am

#399435

Hi Sir,
I would like to sort my some records using order by clause.
CREATE TABLE abc_sort
(ID1 int,
name nvarchar(150),
ID2 int,
IDNUMBER INT,
DATE DATE
)
INSERT INTO abc_sort VALUES(201963,'Office',4900406,2,'1996-07-01');
INSERT INTO abc_sort VALUES(901191,'Office',5308672,2,'2009-08-20');
INSERT INTO abc_sort VALUES(4553448,'School',901191,1,NULL);
INSERT INTO abc_sort VALUES(4595764,'School',201963,1,NULL);
INSERT INTO abc_sort VALUES(8260430,'Office',5355020,2,'1999-01-01');
INSERT INTO abc_sort VALUES(8483620,'School',8260430,1,'1981-04-22');
Request you please help me how to order by my records as like below example
ID1nameID2IDNUMBERDATE
201963Office490040621996-07-01
4595764School2019631NULL
901191Office530867222009-08-20
4553448School9011911NULL
8260430Office535502021999-01-01
8483620School826043011981-04-22

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

What's the logic there? What is it that defines that the first row listed there must come first?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jacob Wilkins One Orange Chip Points: 27972 More actions · Answer 2

Perhaps this?

SELECT*

FROMdbo.abc_sort

ORDER BYCASE

WHEN IDNUMBER = 2 THEN ID1

ELSE ID2

END,

name ASC

--IDNUMBER DESC also works for the secondary sort in the sample data

;

The logic seems strange to me, but that seems to be the logic underlying your desired order in this sample data.

Cheers!

kiran.rajenimbalkar SSCrazy Points: 2603 More actions · Answer 3

Thanks Sir its worked for me thanks for your help 🙂