Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Query Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 11:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 3:40 AM
Points: 3, Visits: 31
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?
Post #1566211
Posted Wednesday, April 30, 2014 12:24 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 790, Visits: 637
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
Post #1566220
Posted Wednesday, April 30, 2014 12:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1566222
Posted Wednesday, April 30, 2014 2:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 7,137, Visits: 13,527
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1566255
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse