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,2 00 etc.
How can I change the behaviour to order the data in numerical order?
Thanks,
Rick 9 2075
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.b logspot.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,2 00 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,2 00 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" <ri**@abasoftwa re.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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,2 00 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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Brendan.Collins |
last post by:
Hi
I have a javascript problem that has been annoying me for two days now
and thought that a javascript expert might have the magic solution.
I am populating a table dynamically from the database and I am trying
to allow the user to order the rows in the table using up and down
arrows.
The function I am using is:
|
by: Mike Nolan |
last post by:
I notice that 7.4 doesn't do default ordering on a 'group by', so you have
to throw in an 'order by' clause to get the output in ascending group order.
Is this something that most RDB's have historically done (including PG prior
to 7.4) but isn't really part of the SQL standard?
On a mostly unrelated topic, does the SQL standard indicate whether NULL
should sort to the front or the back? Is there a way to force it to
one or the other...
|
by: Alexandre H. Guerra |
last post by:
I needed to log all statements executed during a period of time
and now i need ordering the long varchar column in the statements monitor table
(STMT_TEXT)
Is there any flag to set to release the DB2 255 char wide ordering limit ?
Thanks
Alexandre
|
by: Per Rollvang |
last post by:
Hi All!
I use a special ListViewItemComparer-class to sort items in a listview for
any column, and I have also allowed column-reordering.
So far so good, but if I click the columnheader of any column, and the
columnorder is not 'default', the listview 'forget' my column-re-ordering
(i.e 0 2 1), and sets column-order back to default (i.e. 0 1 2).
Is there anyone that might know what I have forgot to code in my
|
by: Marco Castro |
last post by:
I want the users to be able to reorder the columns in a few of my listview
controls. It's easy enough to make that change in the listview control but
I can't seem to find a way to get the new ordering back from the control.
Because of this I don't see any way that I can save the new column order for
them. Is there any way to do this?
| |
by: Matt Roberts |
last post by:
Please accept my apologies if this is answered elsewhere in the archives
or docs but I have searched without luck.
I've always assumed that default ordering of selects are based on a first
in first out principle and that this remains true at the row level despite
edits to columns.
However I'm dealing with a case where this doesn't seem to hold true. The
ordering has changed over time. Its difficult for me to gauge whether the
data has...
|
by: Brian Tkatch |
last post by:
An ORDER BY a simple-integer inside a FUNCTION, results in SQL0440N,
unless the FUNCTION expects an INTEGER as its parameter. For example:
DECLARE GLOBAL TEMPORARY TABLE A(A CHAR(1))
INSERT INTO SESSION.A VALUES ('a'), ('b')
CREATE FUNCTION A(A char(1)) RETURNS char(1) DETERMINISTIC NO EXTERNAL
ACTION RETURN A
SELECT A FROM SESSION.A ORDER BY A(1)
DROP FUNCTION A
DROP TABLE SESSION.A
|
by: cris.b |
last post by:
Hi, I have a question about ordering column in a DataGridView.
I have a databound DataGridView with the AutoGenerateColumn property
set to true.
I want to change column's order.
At runtime I change the DisplayIndex property of each column but seems
it doesn't work!
Column's order is not set!
How to do this?
|
by: ulas |
last post by:
Hi,
I am designing a table in SQL Server 2008 which will contain, in each row, a list of numbers of variable length. The maximum (10) and minimum (2) list lengths are known. The most obvious approach seems to be just creating 10 columns (num1, num2....,num10) and making num3...num10 nullable. Is there a better way of doing this? It just seems wasteful to have so many columns that will be empty most of the time for most of the rows (the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |