Order by in derived table - 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)

Order by in derived table

Author

Topic

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 01:38:03

Hi All,

I have the following query where I need to order the inner select by rank for my Top 10 results to be correct. The ORDER BY clause is invalid though. Is there a better way to order by in the inner select?


SELECT Row_Number() OVER (ORDER BY publication) AS RowID,
count(*) over() as cnt,
ID,
publication,
Rank FROM (
 SELECT ID, publication, KT.Rank FROM Publications AS FT
 INNER JOIN FREETEXTTABLE(Publications, *, @term)
 AS KT ON FT.ID = KT.[Key] WHERE FT.Deleted = 0
	) t 
--ORDER BY Rank Desc, Publication ASC
) d  WHERE RowID >= @start AND RowID <= @outerlimit
	GROUP BY RowID, cnt, ID, publication, Rank ORDER BY Rank DESC, publication ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 01:41:27

[code]
SELECT Row_Number() OVER (ORDER BY publication) AS RowID,
count(*) over() as cnt,
ID,
publication, Rank
FROM (
SELECT ID, publication, KT.Rank
FROM Publications AS FT
INNER JOIN FREETEXTTABLE(Publications, *, @term) AS KT
ON FT.ID = KT.[Key] WHERE FT.Deleted = 0
) t
WHERE RowID >= @start AND RowID <= @outerlimit
GROUP BY RowID, cnt, ID, publication, Rank
ORDER BY Rank DESC, publication ASC
[/code]

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 02:02:35

Thanks Visakh16 oh great guru. Isn't that the same as I have or am I blinded by ignorance?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 02:04:38

quote:
Originally posted by John Sourcer

Thanks Visakh16 oh great guru. Isn't that the same as I have or am I blinded by ignorance?

nope you had an additional derived table which was not required and also Order by was not placed at correct location

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 02:08:41

:S

As usual you are right however RowID isn't a valid column in that query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 02:15:20

quote:
Originally posted by John Sourcer

:S

As usual you are right however RowID isn't a valid column in that query?

oho...Now I understood the purpose of that additional derived table.


SELECT *
FROM
(
SELECT Row_Number() OVER (ORDER BY publication) AS RowID, 
count(*) over() as cnt, 
ID, 
publication, Rank 
FROM (
	SELECT ID, publication, KT.Rank 
        FROM Publications AS FT 
        INNER JOIN FREETEXTTABLE(Publications, *, @term) AS KT  
        ON FT.ID = KT.[Key] WHERE FT.Deleted = 0
	) t 
)d
WHERE RowID >= @start AND RowID <= @outerlimit
GROUP BY RowID, cnt, ID, publication, Rank 
ORDER BY Rank DESC, publication ASC

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 02:21:48

:(

That won't work because it's the inner select that needs sorting. That's my problem, great yak herder.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 02:26:52

quote:
Originally posted by John Sourcer

:(

That won't work because it's the inner select that needs sorting. That's my problem, great yak herder.

sorry didnt get that. whats significance of order by in inner query? you're returning everything so you dont need to worry about order until final retrieval statement. Am i missing anything here?

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 02:28:17

I'm returning a limited row set i.e. @start = 0 and @outerlimit = 10 so I need the top 10 in the inner select.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 02:31:11

quote:
Originally posted by John Sourcer

I'm returning a limited row set i.e. @start = 0 and @outerlimit = 10 so I need the top 10 in the inner select.

but thats what you do by means of below condition isnt it?

WHERE RowID >= @start AND RowID <= @outerlimit

or do you mean you need to consider these also before you do above selection?

ORDER BY Rank Desc, Publication ASC

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 02:41:31

Correct, I first have to ORDER BY and then get the row limits. Another derived table?

Edit: Obviously not.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 03:45:28

Totally stumped on this one. Where are the SQL can do anything guys. I've never seen SQL fail myself.

John Sourcer
Yak Posting Veteran

91 Posts

Posted - 2010-01-14 : 04:00:14

AHA!

SELECT TOP x in your inner select where x is the row count!

Kristen
Test

22859 Posts

Posted - 2010-01-14 : 04:06:28

"Where are the SQL can do anything guys."

Visakh is here

Personally I'm just sitting and waiting for his cross-apply solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 05:06:45

quote:
Originally posted by John Sourcer

AHA!

SELECT TOP x in your inner select where x is the row count!

so where you looking for this?


SELECT *
FROM
(
SELECT Row_Number() OVER (ORDER BY publication) AS RowID, 
count(*) over() as cnt, 
ID, 
publication, Rank 
FROM (
	SELECT TOP x ID, publication, KT.Rank 
        FROM Publications AS FT 
        INNER JOIN FREETEXTTABLE(Publications, *, @term) AS KT  
        ON FT.ID = KT.[Key] WHERE FT.Deleted = 0
        ORDER BY Rank DESC, publication ASC
	) t 
)d
WHERE RowID >= @start AND RowID <= @outerlimit
GROUP BY RowID, cnt, ID, publication, Rank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources