Arranging table output

Question

Post reply

Arranging table output

Viewing 4 posts - 16 through 18 (of 18 total)

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

SkyBVI SSCoach Points: 15128 More actions · Answer 1

@ gail

"It'll just give you a convenient column to Order By"

Does that mean identity column??

I heard that indexing on an ORDER BY can help in maintaing the order in a new table.

is that correct?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SKYBVI (10/15/2010)
Does that mean identity column??

No. I mean that if you use the Row_Number to add a 'sequence' column all that does for you is gives you a column that you can use in the Order By statement when you query the table.

I heard that indexing on an ORDER BY can help in maintaing the order in a new table.

There is no such thing as order of rows in a table. A table, by definition is an unordered set of data. If you query a table, the rows can come back in any order whatsoever unless you specify an ORDER BY.

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

drew.allen SSC Guru Points: 76988 More actions · Answer 3

Maybe what you really want to do is create a view. That way you can use the trick of specifying the TOP (100) PERCENT, so that you can specify an order by.

CREATE VIEW YourView

AS

SELECT TOP (100) PERCENT

Product_ID, Name, Description,Category, Image_Path, price

from Webitems_test1

ORDER BY Category, Price DESC

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

drew.allen (10/15/2010)
Maybe what you really want to do is create a view. That way you can use the trick of specifying the TOP (100) PERCENT, so that you can specify an order by.

You can specify order by. No version of SQL past 2000 will honour the Order By, but you can specify it.

In SQL 2005+ the only Order by that is guaranteed to order the returned rows is the Order By on the outermost select statement that's returning data.

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