Logical sorting issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Logical sorting issue

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-05-05 : 03:58:14

Hi, I am trying to understand the behaviour of sorting here. I know without orderby the resultset may be in any order but the question is why is the resultset sorted in the last query below.
Expected output as per TOP 5 and then ORDER BY SALES DESC should be=4000. query 1 and 2 are not ordering in any order and thus different output but why is query 3 giving the correct result without any orderby clause:

IF OBJECT_ID('tempdb.dbo.#Temp') IS NOT NULL DROP TABLE #Temp

CREATE TABLE #Temp (
LAST_NAME VARCHAR(255),
SALES MONEY
)

INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Infrared', 400)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Red', 600)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Orange', 100)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Yellow', 300)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Green', 800)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Violet', 500)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Blue', 700)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Ultraviolet', 1000)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('X-Rays', 200)
INSERT INTO #Temp (LAST_NAME, SALES) VALUES ('Gamma Rays', 900);

-- 1. RESULT=2200=SUMMING UP TOP 5 RECORDS AS THEY WERE INSERTED
WITH CTE AS (
SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY SALES DESC) AS 'RANK',SALES FROM #Temp
)
SELECT SUM(SALES) FROM CTE

-- 2. RESULT=SAME AS ABOVE=2200=SUMMING UP TOP 5 RECORDS AS THEY WERE INSERTED
SELECT SUM(SALES) FROM(
SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY SALES DESC) AS 'RANK',SALES FROM #Temp
)T

--3. PUT OUTPUT IN TEMP TABLE=RESULT=4000 WHICH IS WHAT WE WANT IF ---SORTED BY SALES DESC BUT THERE IS NO ORDERBY CLAUSE HERE. WHAT AM I MISSING HERE?
IF OBJECT_ID('tempdb.dbo.#P') IS NOT NULL DROP TABLE #P
SELECT TOP 5 ROW_NUMBER() OVER (ORDER BY SALES DESC) AS 'RANK',SALES INTO #P FROM #Temp
SELECT SUM(SALES) FROM #P

--------------------
Rock n Roll with SQL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-05 : 04:09:36

Depending on the operators that the query optimiser selects, the resultset may be sorted by one column or another. It's utterly dependent on the plan though and cannot be guaranteed to remain the same if the optimal plan changes

--
Gail Shaw
SQL Server MVP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-05 : 04:10:49

without ORDER BY the sequence of the result return is not guaranteed

KH
[spoiler]Time is always against us[/spoiler]

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-05-05 : 04:54:50

Ok so I can assume the final output (which seems to be correct) is just by chance that the optimizer picked up the plan as per the sort I need and has nothing to do with row_number or anything else sorting it correctly. One more point to note here is, if I do a Select * from CTE (in query1) then the records are actually ordered by SALES desc. Why is this so?

--------------------
Rock n Roll with SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 05:14:33

To make your life easier you should stop asking yourself why a result set looks like it is without any ORDER BY clause.
This behaviour is never reliable so don't waste your time

No, you're never too old to Yak'n'Roll if you're too young to die.

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-05-05 : 05:22:25

Yes maybe you are right but to get the final query result correctly made me think maybe there was more to it. The optimizer fooled me here.

--------------------
Rock n Roll with SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-05 : 05:33:08

-- 4. no need for ROW_NUMBER(), just do an ORDER BY to get your result
SELECT SUM(SALES) FROM(
SELECT TOP 5 SALES FROM #Temp ORDER BY SALES DESC
)T

No, you're never too old to Yak'n'Roll if you're too young to die.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-05 : 05:37:08

Look at the execution plan, it will show you where the sort is.

It's most likely the result of the row_number, SQL sorted the resultset to allocate those values, and had no reason to resort it afterwards and had no order-mangling operators after. There are several operators that sort the internal result set for whatever reason (merge join, stream aggregate among others)

--
Gail Shaw
SQL Server MVP

rocknpop
Posting Yak Master

201 Posts

Posted - 2011-05-05 : 05:41:54

Thanks

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources