ordering an int column

I've created a clustered two column index on a table where the second
column is an integer value.  When the first column is the same, instead
of ordering in numerical order it is ordering 1,10,100,2,20,200 etc.
How can I change the behaviour to order the data in numerical order?

Thanks,
Rick

Take a look at this
Run it in Query Analyzer

-- not ordered as int
select '1'
union all
select '2'
union all
select '11'
order by 1
-- ordered as int
select convert(int,1)
union all
select convert(int,2)
union all
select convert(int,11)
order by 1

--http://sqlservercode.blogspot.com/

Rick  wrote:
> I've created a clustered two column index on a table where the second
> column is an integer value.  When the first column is the same,
> instead of ordering in numerical order it is ordering
> 1,10,100,2,20,200 etc. How can I change the behaviour to order the
> data in numerical order?

Did you actually declare the second column to be of type int or is it a
varchar and values represent integers?  If it's the latter then I wouldn't
be surprised...

robert

It would be useful if you posted CREATE TABLE and CREATE INDEX
statements to show exactly what you're doing - is the first column a
character data type? What does the data look like in both columns? Does
your query include an ORDER BY?

Assuming that your goal is to return data in a certain order, then you
can't do it by using a clustered index - the only way is to use ORDER
BY in your queries.

Simon

The relevant table structure is
col 1 -type datetime
col 2 -type int

In Enterprise manager, I've created a two column clustered index
with col 1 as the first column of the index in desc order
and with col 2 as the second column of the index in asc order

When I open the data, for a common date, it orders the data
1,10,100,2,20,200 etc.
Rick

Ordering of results is guaranteed only when you specify an ORDER BY clause,
regardless of any indexes you have on the table.  As Simon indicated, you
need to specify  ORDER BY for your ordering requirement.


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Rick" <rick@abasoftware.com> wrote in message
news:1127750481.808920.194390@g49g2000cwa.googlegroups.com...
> The relevant table structure is
> col 1 -type datetime
> col 2 -type int
>
> In Enterprise manager, I've created a two column clustered index
> with col 1 as the first column of the index in desc order
> and with col 2 as the second column of the index in asc order
>
> When I open the data, for a common date, it orders the data
> 1,10,100,2,20,200 etc.
> Rick
>

I'm not so sure it isn't a bug.
Here are some observations.
If I write a select statement like
'Select * from mytable order by datecolumn, intcolumn', the int column
order is correct.
If I write a select statement like
'Select * from mytable order by datecolumn desc, intcolumn', the int
column order is not correct.
This parallels the (mis) behaviour of the clustered index.
Is there a logical reason for this or is this a bug?

Rick  wrote:
> I'm not so sure it isn't a bug.
> Here are some observations.
> If I write a select statement like
> 'Select * from mytable order by datecolumn, intcolumn', the int column
> order is correct.
> If I write a select statement like
> 'Select * from mytable order by datecolumn desc, intcolumn', the int
> column order is not correct.
> This parallels the (mis) behaviour of the clustered index.
> Is there a logical reason for this or is this a bug?
>

I suggest you post a CREATE TABLE/INDEX and INSERT script which
reproduces the issue (and of course your MSSQL edition, version and
servicepack), otherwise we have to make guesses about what your table
structure, data and query look like. It's not really clear from your
description what you're seeing, nor what you consider correct behaviour
to be in this case - if you provide something which can be copied and
pasted into Query Analyzer, you'll probably get a better response.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

Rick  wrote:
> I'm not so sure it isn't a bug.
> Here are some observations.
> If I write a select statement like
> 'Select * from mytable order by datecolumn, intcolumn', the int column
> order is correct.
> If I write a select statement like
> 'Select * from mytable order by datecolumn desc, intcolumn', the int
> column order is not correct.
> This parallels the (mis) behaviour of the clustered index.
> Is there a logical reason for this or is this a bug?

Not at all: DBMS are free to return results in *any* order unless there is
an ORDER BY.  As simply as that.  Even though you often get expected
results when querying columns from a clusterd index there's no guarantee
that they will be ordered.

Regards

robert

On 26 Sep 2005 10:20:36 -0700, Rick wrote:

>I'm not so sure it isn't a bug.
>Here are some observations.
>If I write a select statement like
>'Select * from mytable order by datecolumn, intcolumn', the int column
>order is correct.
>If I write a select statement like
>'Select * from mytable order by datecolumn desc, intcolumn', the int
>column order is not correct.
>This parallels the (mis) behaviour of the clustered index.
>Is there a logical reason for this or is this a bug?

Hi Rick,

If you include the ORDER BY clause, the results should be in the
requested order. Anything else would be a bug. But first: what program
are you using to run these queries? How does that program display the
values from the date column - are hours, minutes, seconds and
milliseconds included? Becuase they ARE considered in the sort
operation; if they are then cut-off in the display of your client
program, the results might APPEAR to be ordered incorrectly. If you're
unsure, re-run the query using Query Analyzer.

If that doesn't help, then I'd very much like to see a repro script,
consisting of CREATE TABLE statements, some INSERT statements to set up
the data and the SELECT statements that cause the suspected buggy
behaviour. See www.aspfaq.com/5006.

Also, include the output of SELECT @@VERSION in your next post, please.

Best, Hugo
--

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