ascending/Descending other than Order By clause?

Question

ascending/Descending other than Order By clause?

mahesh_jm

SSC Rookie

Points: 44
More actions
November 6, 2002 at 9:43 am

#79857

Is there any other way to get the columns in ascending/Descending other than Order By clause?
Edited by - mahesh_jm on 11/06/2002 09:46:46 AM

Maheshwar

Viewing 10 posts - 1 through 9 (of 9 total)

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

If you create a clustered index on the column you want the data sorted by, then the data will be returned in asc order based on that column.

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 2

Returned in order of clustered index, could be desc.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Other than that you could use a subquery comparison but they are extremly slow.

shendricks SSChasing Mays Points: 635 More actions · Answer 4

A table is an UN-ordered set of data. Even with a clustered index, the data is NOT guaranteed to be returned in order. It often will come back in order (no real surprise) but does not always.

If you want the data in a specific order, you need to explicitly ask for it (e.g., ORDER BY).

Steve Hendricks

MCSD, MCDBA

AFS Consulting Group

shendricks@afsconsulting.com

(949) 588-9800 x15

Steve Hendricks
MCSD, MCDBA
Data Matrix

shendricks@afsconsulting.com
(949) 588-9800 x15

dj_meier SSCrazy Points: 2732 More actions · Answer 5

Typically data can be returned in the order of the clustered index without the use of ORDER BY, but there are some exceptions:

1) If the optimizer determines that the cost of using the clustered index is greater than using another index, it may come back in the order of the lowest cost index. If you have a wide compound index, then the cost of reading that would be higher that a non-clustered single Integer index.

2) If you have multiple processors, the optimizer may use multiple worker threads to execute the SELECT. When they are merged together, they could be in a shuffled order.

You may be able to use a forceindex on the clustered index to make sure it uses it.

-Dan

mark hutchinson SSC Eights! Points: 878 More actions · Answer 6

also,

Select Distinct ...

will cause the output to be displayed in ascending sequence. If the first column is numeric, you can multiply the returned column by -1 to cause the results to be displayed in descending sequence.

mahesh_jm SSC Rookie Points: 44 More actions · Answer 7

Hi,

Thanks for helping me. It will be helpful to me.

Maheshwar

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Mark,

DISTINCT does not cause the retrieved data to be sorted in any way. I have used SELECT DISTINCT in several queries and the data is NEVER returned sorted unless I use ORDER BY. For example, I did a SELECT DISTINCT LNAME FROM mytable and the names were not in alphabetical order or any other order.

I think you might have GROUP BY and ORDER BY mixed up. SELECT DISTINCT works the same as GROUP BY (when GROUP BY is used without an aggregate).

-Bill

Steve Rosenbach SSCrazy Points: 2181 More actions · Answer 9

Be careful with counting on GROUP BY to do ordering with SQL 2000. This may still work on version 7.0 and below, but you can't really tell beforehand if a query will return an ordered resultset anymore with GROUP BY with SQL 2000. Apparently, this is something the optimizer will figure out on its own if it wants to do or not.

Best regards,

SteveR