SQL Query

Question

Post reply

SQL Query

bspavan86

SSC Enthusiast

Points: 166
More actions
April 29, 2014 at 11:57 pm

#285014

CREATE TABLE OFFICES(
OFFICEID INT NOT NULL,
OFFICENAME VARCHAR(100),
HEADOFFICEID INT
)
INSERT INTO OFFICES VALUES(1,'Germany',0);
INSERT INTO OFFICES VALUES(2,'France',0);
INSERT INTO OFFICES VALUES(3,'USA',0);
INSERT INTO OFFICES VALUES(115,'Berlin',1);
INSERT INTO OFFICES VALUES(116,'Munich',1);
INSERT INTO OFFICES VALUES(117,'Cologne',1);
INSERT INTO OFFICES VALUES(118,'Lyon',2);
INSERT INTO OFFICES VALUES(119,'Marseille',2);
INSERT INTO OFFICES VALUES(120,'Paris',2);
INSERT INTO OFFICES VALUES(121,'San Francisco',3);
INSERT INTO OFFICES VALUES(122,'Boston',3);
INSERT INTO OFFICES VALUES(123,'Houston',3);
select *
from offices o
order by (case when headofficeid = 0 then officeid else headofficeid end),
(case when headofficeid = 0 then 1 else 2 end),
officeid;
Hi can someone explain how the above Select Query works internally?

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

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

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 1

Hi,

The Query execution is simmilar to below query

select *,case when headofficeid = 0 then officeid else headofficeid end [FirstSort],case when headofficeid = 0 then 1 else 2 end [SecondSort]

from offices o

order by [FirstSort],[SecondSort],officeid

Regards,
Mitesh OSwal
+918698619998

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Refer the execution plan for further understanding.

However general flow of query is like this...

Query > Algebrizer > Optimizer > Execution > output

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Assuming you are referring to the ORDER BY clause, then visualising the result of those CASEs will help:

DROP TABLE #OFFICES

CREATE TABLE #OFFICES (

OFFICEID INT NOT NULL,

OFFICENAME VARCHAR(100),

HEADOFFICEID INT

)

INSERT INTO #OFFICES VALUES

(1,'Germany',0),

(2,'France',0),

(3,'USA',0),

(115,'Berlin',1),

(116,'Munich',1),

(117,'Cologne',1),

(118,'Lyon',2),

(119,'Marseille',2),

(120,'Paris',2),

(121,'San Francisco',3),

(122,'Boston',3),

(123,'Houston',3)

SELECT

o.*,

'#' '#', -- Cosmetic divider between columns from 'o' and columns from 'x'

x.*

FROM #offices o

CROSS APPLY (

SELECT

OrderBy1 = CASE WHEN headofficeid = 0 THEN officeid ELSE headofficeid END,

OrderBy2 = CASE WHEN headofficeid = 0 THEN 1 ELSE 2 END, -- redundant

OrderBy3 = officeid

) x

ORDER BY

OrderBy1,

OrderBy2,

OrderBy3;

Note that the second ORDER BY clause is redundant.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden