Re: why the result of the query is in asending order

On Mon, 18 Apr 2005 04:21:02 -0700, bhavik wrote:

(snip)
>i want my result to be
>
>in the following way(which is the actual order in routesegment : desirable)
>
>
>locationname locationid
>---------------------------------------
>Gallery 20
>loc2 25
>loc1 24
>WP2 27
>WP1 26
>wp3 28
>
>
>why the result of the query is in asending order

Hi Pravin,

Since you didn't specify an ORDER BY clause in your query, SQL Server is
free to return the rows in any order it sees fit. The optimizer will
create an execution plan that will minimize the execution time, without
regard for the order in which rows are returned. Explaining why this
particular order is used requires full knowledge of your tables, all
indexes, statistics about numbers of rows and data distribution, details
of your server, other processes running at the same time and a fair
amount of guessswork.

>
>is ther any solution

The only way to make sure you get the rows in the order you need them is
to specify an ORDER BY clause. I'm not sure if I understand your data
correctly, but I *think* that this one will return the rows in the order
you want them:

SELECT
distinct locationname,locationid
FROM
routesegment
,location

WHERE
(startlocation=locationid
or
endlocation=locationid)
and
routeid=@routeid
ORDER BY rsid -- <-- Add this line

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.