delete duplicate orders

I have an order with multiple line items. When the order comes in and put into a table I would like to check that they haven't submitted it twice. Right now I have a stored procedure that can find duplicate lines, but I really need if the whole order ha
s been duplicated then delete it.
So in my store procedure I use:
SELECT COUNT(*) AS Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity
FROM tblItemOrder
GROUP BY ItemNumber,Store,DeliveryDate,submission,Quantity
HAVING COUNT(*) > 1
So this finds duplicate rows. But I need to find if the all rows are duplicated go ahead and delete.
The table structure is fields Amount,ItemNumber,Store,DeliveryDate,submission,Qu antity with no primary key (working table to get the data into shape).
Even if I could find if there are the same amount of rows that have duplicates COUNT(*) > 1, then go ahead and delete the duplicates that would work fine.
hi ashley,
See following example:
--sample data.
create table #cartype(manufacturer varchar(500), itemnumber int)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Toyota',1)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',2)
insert into #cartype values('Honda',3)
insert into #cartype values('GE',3)
--deleting all duplicate rows from the table, query will be.
delete a
from #cartype a join
(select manufacturer, itemnumber
from #cartype
group by manufacturer, itemnumber
having count(*) > 1) b on a.manufacturer = b.manufacturer and
a.itemnumber = b.itemnumber
--if you want to keep one row out of the duplicate rows, you will have to
add an identity column to the table.
Ex:
alter table #cartype add idd int identity
--deleting duplicate rows from the table except one
delete from #cartype
where not exists
(Select * from #cartype a
where a.manufacturer = #cartype.manufacturer
and a.itemnumber = #cartype.itemnumber
having min(idd) = #cartype.idd)
--drop the temporary added column
alter table #cartype drop column idd
Vishal Parkar
vgparkar@.yahoo.co.in

Defining sort order using parameter fields.

Hi!

This is my first post at this forum.

I have a parameterfield wich the users can use to enter sortorder to the report. I have then created a formula with this code in it.

if {?sortid} = "1" then ({DMD_VIEW_ERRAND.er_control_date} AND {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} AND {DMD_ERRAND.ER_HANDLERDATA} AND {DMD_VIEW_ERRAND.er_id})


This formula is then added into the record sorting expert.

The problem is there is an error in the formula. I want to order by multiple columns. Is it possible?
If it is, what's wrong with my code?

I'm using crystal XI
Best regards
HeleniusI've solved it.

Here's the code

if {?sortid} = "1" then (CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})
else
if {?sortid} = "2" then ({DMD_VIEW_ERRAND.er_activity_desc} + "AND" + {DMD_ERRAND.ER_HANDLERDATA} + "AND" + CStr({DMD_VIEW_ERRAND.er_control_date_fmt}) + "AND" + {DMD_VIEW_ERRAND.er_id})

I had a little convertion problem...

/Helenius|||Noooo, it doesn't seem to be solved after all. The report only sort based by the first column. Any suggestions??

Define population order

Hi,
Is it possible to define a specific order for full-text data to be
populated ? I've got a few hundred thousands rows indexed that can be
completely renewed sometimes... When this "big update" happens, I
always rebuild the FT index, to avoid delivering inaccurate results
(as almost all the key changed), and start a full population.
In these rows, some of them (15%) contains almost all the revelant
information, and all the others (85%) contains only complementary
information. Is there a way to force the FT index to index the 15%
interesting rows first, and then to complete with the 85%. That way, I
could deliver accurate information asap...
Does someone knows how FT determines the order to populate data in the
index ? I tried to modify the data order in the table where FT is
enabled, but it seems to be useless..
Thanks,
Fred
Not really. The crawl or population is done on a per table basis. For both
the incremental and full population it starts with row 1 and then progresses
to the last rows.
Your only solution would be to partition the table into two or more tables
and then you can schedule the population on a per table basis.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408152032.617400c4@.posting.google.c om...
> Hi,
> Is it possible to define a specific order for full-text data to be
> populated ? I've got a few hundred thousands rows indexed that can be
> completely renewed sometimes... When this "big update" happens, I
> always rebuild the FT index, to avoid delivering inaccurate results
> (as almost all the key changed), and start a full population.
> In these rows, some of them (15%) contains almost all the revelant
> information, and all the others (85%) contains only complementary
> information. Is there a way to force the FT index to index the 15%
> interesting rows first, and then to complete with the 85%. That way, I
> could deliver accurate information asap...
> Does someone knows how FT determines the order to populate data in the
> index ? I tried to modify the data order in the table where FT is
> enabled, but it seems to be useless..
> Thanks,
> Fred
|||> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then progresses
> to the last rows.
Ok thanks Hilary for the reply. I've made some tests and here are some
interesting results. The population starts on row 1 and then
progresses to the last rows only if you don't have any integer indexes
(standard indexes, not FT ones) defined on the table. If you've got
indexes on int columns, it takes the last one to determine the crawl
order...
It means that if you want to specify the order the index should be
populated, it is possible to create a int column 'priority', for
example with values from 1 to 100 and create an index on it (it should
be the last one listed in enterprise manager). The sorting method of
the index can also reverse the crawl order !
So that's great, but I can't figure out why Microsoft doesn't specify
more about this : it could be useful in many scenarios.
Fred
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
> Not really. The crawl or population is done on a per table basis. For both
> the incremental and full population it starts with row 1 and then
progresses
> to the last rows.
>
What defines "row 1" I assume the primary key used as part of the FT?
Does setting this with ASC or DESC make any difference?

> Your only solution would be to partition the table into two or more tables
> and then you can schedule the population on a per table basis.
>
|||Very interesting research Fred!
Is it possible that this last index is the unique index that you have
created for Full Text Indexing?
The way it works is that SQL FTI issues a
exec sp_fulltext_getdata 5, 1223675407
where 5 is the db_id and the number is the number in sysobjects that
corresponds to the object_id of the table or id in sysobjects.
This call returns a row set which is stored in ram and it is ordered by
whatever index you are using as your unique index, and this is the order in
which rows are extracted AFAIK.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Fred" <flaignel@.yahoo.fr> wrote in message
news:fd23b8be.0408161433.20a75ae7@.posting.google.c om...[vbcol=seagreen]
both[vbcol=seagreen]
progresses
> Ok thanks Hilary for the reply. I've made some tests and here are some
> interesting results. The population starts on row 1 and then
> progresses to the last rows only if you don't have any integer indexes
> (standard indexes, not FT ones) defined on the table. If you've got
> indexes on int columns, it takes the last one to determine the crawl
> order...
> It means that if you want to specify the order the index should be
> populated, it is possible to create a int column 'priority', for
> example with values from 1 to 100 and create an index on it (it should
> be the last one listed in enterprise manager). The sorting method of
> the index can also reverse the crawl order !
> So that's great, but I can't figure out why Microsoft doesn't specify
> more about this : it could be useful in many scenarios.
> Fred
|||I believe its the first row ordered by the unique index that SQL FTS
requires.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:9XeUc.17$2s.14@.twister.nyroc.rr.com...[vbcol=seagreen]
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23oKC%23d4gEHA.3536@.TK2MSFTNGP12.phx.gbl...
both[vbcol=seagreen]
> progresses
> What defines "row 1" I assume the primary key used as part of the FT?
> Does setting this with ASC or DESC make any difference?
>
tables
>
|||Hilary, I *believe* you meant to say the first and only column in the
single, non-nullable column required for the 'regular' unique index that SQL
FTS requires.
Fred, I would agree with Hilary that this is most interesting research!
Could you provide more details about your table's structure via the
following SQL script?
use <your_database_name_here>
go
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help <your_FT-enable_table_name_here>
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uK9$xTGhEHA.2684@.TK2MSFTNGP10.phx.gbl...
> I believe its the first row ordered by the unique index that SQL FTS
> requires.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in
message
> news:9XeUc.17$2s.14@.twister.nyroc.rr.com...
> both
> tables
>

Define a hierarchy in sql script for SQL SERVER 2005

Good day to all!

I have a problem to resolve.

Which is the script in sql in order to define one hierarchy of products categories?

I have need to define the script for the creation of the table and store procedures (INSERT,DELETE,UPDATE,GET,GETALL).

Thanks for the eventual answers !

Search Google for Joe Celko's articles about hierarchies in SQL Server.

You ma yalso be interested in following links:

Joe Celko's book about hierarchies
Article about trees in SQL

|||

Xml is a suitable way to store hierarchies.

So you could define category hierarchies in xml and load it into database.

Hope this helps.

Default sort order when using SELECT with no ORDER BY clause

For SQL Server 2000 and 7.0:

What is the default sort order of a dataset when you perform a SELECT * FROM [tablename] without using the ORDER BY clause?

I've been told it is ordered by the clustered index (my testing does not bear this out) and I've been told that it is completely unpredictable (closer to what I'm seeing). I believe the latter to be true but cannot find this outlined anywhere in Microsoft's BOL, TechNet, KnowledgeBase, MSDN, etc.

Any pointers on where to find the definitive answer is appreciated.

TIA. RickIf there is clustered index - sorting by clustered index, else sorting by inserting records.

BOL says:

SQL Server 7.0 tables use one of two methods to organize their data pages:

Clustered tables are tables that have a clustered index.
The data rows are stored in order based on the clustered index key. The data pages are linked in a doubly-linked list. The index is implemented as a B-tree index structure that supports fast retrieval of the rows based on their clustered index key values.

Heaps are tables that have no clustered index.
The data rows are not stored in any particular order, and there is no particular order to the sequence of the data pages. The data pages are not linked in a linked list.

SQL Server also supports up to 249 nonclustered indexes on each table. The nonclustered indexes have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes have no effect on the order of the data rows. Clustered tables keep their data rows in order based on the clustered index key. The collection of data pages for a heap is not affected if nonclustered indexes are defined for the table. The data pages remain in a heap unless a clustered index is defined.|||Hi ,

If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted.

If you mention ORDER BY Column name , then the dafault sort order will be ASCending if SQL Server is installed by default collation.

Ex. Select * from authors order by authorname

FYI:
--
Use the locale identified by Setup, and then choose the desired binary, case, or other options.

For the release of SQL Server 2000, when Setup detects that the computer is running the U.S. English locale, Setup automatically selects the SQL collation: Dictionary order, case-insensitive, for use with 1252 character set.

To select the equivalent Windows collation, select Collation designator, choose the Latin1_General collation designator, do not select case-sensitive, and select accent-sensitive.

For more information , see BOL - Collation|||"If there is no ORDER BY clause in the Query , then the data will be in the same order as inserted"

if you're lucky

records can get physically stored not in insertion sequence (e.g. a row happens to be too big for the current physical database page, but the next one inserted after that isn't)

i believe records are returned in physical sequence, all things being equal

but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first

i too have heard that the sort order is unpredictable, but i too have been unable to find this on microsoft's site

a "definitive" answer will be found, of course, only on microsoft's site

rudy
http://rudy.ca/|||Hi,

I dont agree with "R937". Sorry to say that.

To his Question" but what if some of the rows are already in cache, and the remaining rows need to be fetched from disk? it's a safe bet that the ones in cache are pumped out first"

This is absolutely wrong. Thats not the concept of caching or Query Optimization.

For Example, if I excute the query as below,

Use pubs
go
/* This will return rows where job_id > 5 */
Select * from jobs where job_id > 5
go

Select * from jobs
go
AS per your statement, this has to return the jobid>5 from cache first and then job_id<4 from disk.

Thats not true.

Concept:
----
As per SQL Server, both the query are entirely different.

First it will just look in cache whether the same SQL Query is available in cache not the data. If the SQL Query is not avaiable , then it will be excuted for optimization and return the rows from disk.

Hope you agree with me.

Any constructive criticism will be appreciated.

FYI: Concepts on Query Optimization will explain well.

Have Fun :)
Varad

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
Smith
The results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>
|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>
|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.

> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)

> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.

> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional
>
|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:[vbcol=seagreen]
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> the
> additional

Default Sort column....

My query looks like this......
SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
How the rest of the data will be displayed? How exactly SQL Server
determines the sorting order columns for rest of the columns?
I am using SQL2K.
Thanks,
SmithThe results will be ordered by COL2 only. If the values are not unique, the
ordering of the remaining data is undefined. You need to specify additional
columns in your ORDER BY clause if you want other data returned in a
particular sequence.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> My query looks like this......
> SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> How the rest of the data will be displayed? How exactly SQL Server
> determines the sorting order columns for rest of the columns?
> I am using SQL2K.
> Thanks,
> Smith
>|||Dan,
Thanks for the reply.
1. Is it documented anywhere that remaining sort order is undefined?
2. Is it consistent while displaying the data when the order is undefined?
Thanks,
Smith
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> The results will be ordered by COL2 only. If the values are not unique,
the
> ordering of the remaining data is undefined. You need to specify
additional
> columns in your ORDER BY clause if you want other data returned in a
> particular sequence.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > My query looks like this......
> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > How the rest of the data will be displayed? How exactly SQL Server
> > determines the sorting order columns for rest of the columns?
> > I am using SQL2K.
> > Thanks,
> > Smith
> >
> >
>|||> 1. Is it documented anywhere that remaining sort order is undefined?
Not as far as I know. As a rule, undefined behavior is seldom documented.
It is risky to rely on undefined/undocumented behavior because this can
change without notice between versions or service packs and break your code.
> 2. Is it consistent while displaying the data when the order is undefined?
No. Once your ordering criteria is satisfied, the order of the remaining
data depends on the details of the query plan. This may vary depending on
the indexes used, number of processors, concurrent scans, etc. SQL Server
certainly doesn't add the unnecessary overhead of sequencing data without an
explicit ORDER BY unless it is needed for internal query optimization
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
>> The results will be ordered by COL2 only. If the values are not unique,
> the
>> ordering of the remaining data is undefined. You need to specify
> additional
>> columns in your ORDER BY clause if you want other data returned in a
>> particular sequence.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Smith John" <JohnSmith56@.hotmail.com> wrote in message
>> news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
>> > My query looks like this......
>> > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
>> > How the rest of the data will be displayed? How exactly SQL Server
>> > determines the sorting order columns for rest of the columns?
>> > I am using SQL2K.
>> > Thanks,
>> > Smith
>> >
>> >
>>
>|||"Smith John" <JohnSmith56@.hotmail.com> wrote in message
news:Om%23p8Xb2EHA.2016@.TK2MSFTNGP15.phx.gbl...
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
Yes. In theory: Sets have no order
In SQL 2000 selects return no specific order unless you use an Order BY.
Part of this is because it may be faster for the DB to return it in an order
different from what you want. (for example SOMETIMES you can expect it to
return in the order of the clustered index, but that's simply because it can
read it off the disk faster that way.)
> 2. Is it consistent while displaying the data when the order is undefined?
No. Or rather, it's not guaranteed to be. Now, in my experience, usually
it is, but don't count on it.
> Thanks,
> Smith
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >
>|||With an SQL query, you specify the resultset. It is up to the RDBMS to
generate the correct result, and it can obtain this result any which way
it likes, just as long as the result is correct.
Everything you don't specify (such as the ordering of two rows with the
same value in column COL2) is by definition unspecified. This is a
property of SQL.
What SQL-Server will do is create a query plan that ensures the ordering
on COL2. The order for duplicates COL2-rows will just be in whatever
order the rows happen to be at that point. Since (in most cases) there
are many ways to achieve the same result, you cannot rely on any
particular order that you did not specify.
Gert-Jan
Smith John wrote:
> Dan,
> Thanks for the reply.
> 1. Is it documented anywhere that remaining sort order is undefined?
> 2. Is it consistent while displaying the data when the order is undefined?
> Thanks,
> Smith
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OMaxZUb2EHA.4028@.TK2MSFTNGP15.phx.gbl...
> > The results will be ordered by COL2 only. If the values are not unique,
> the
> > ordering of the remaining data is undefined. You need to specify
> additional
> > columns in your ORDER BY clause if you want other data returned in a
> > particular sequence.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Smith John" <JohnSmith56@.hotmail.com> wrote in message
> > news:OIPh$Mb2EHA.1452@.TK2MSFTNGP11.phx.gbl...
> > > My query looks like this......
> > > SELECT COL1,COL2,COL3,COL4 FROM TABL ORDER BY COL2 ASC
> > > How the rest of the data will be displayed? How exactly SQL Server
> > > determines the sorting order columns for rest of the columns?
> > > I am using SQL2K.
> > > Thanks,
> > > Smith
> > >
> > >
> >
> >