ROWNUM and ORDER BY

Am using Oracle 9i.

Just wanted to know in which order the db will execute my query if my query contains a 'WHERE ROWNUM < 1000' and an 'ORDER BY ...'.
The documentation says that the order of evauation depends upon the indexes used in the ORDER BY, but doesn't specify clearly in which order.

Please help.You can't use ROWNUM and ORDER BY in the same select because the pseudo column ROWNUM is affected before the sort.

So, you have to do :

Select ... FROM (SELECT ... FROM ... ORDER bY...) WHERE ROWNUM<1000

Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

row_number query

If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegrou
ps.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

row_number query

shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroup
s.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?|||shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to
the same value for
every row. This means that SQL Server can access the data in any way it find
s most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a col
umn which is unique.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroup
s.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

row_number query

If i run the query
with test as
(select *, row_number() over (order by user_name()) as cnt
from table1)
select * from test order by cnt
Will i always get rows in same order every time when I run the query?shahdha...@.gmail.com wrote:
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
no, it is not guaranteed to be the same.|||No, you're ordering by a constant.
If you want to return the rows in an order that represents something in the
data, why don't you order by one of the data columns?
<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||No. You use a function in the OVER clause for the ORDER BY which resolves to the same value for
every row. This means that SQL Server can access the data in any way it finds most efficient. In
short, the ROW_NUMBER function is not deterministic unless you specify a column which is unique.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<shahdharti@.gmail.com> wrote in message news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>|||<shahdharti@.gmail.com> wrote in message
news:1150815893.641027.46750@.b68g2000cwa.googlegroups.com...
> If i run the query
> with test as
> (select *, row_number() over (order by user_name()) as cnt
> from table1)
> select * from test order by cnt
> Will i always get rows in same order every time when I run the query?
>
No. You get what you ask for. Add the key of Table1 to the OVER ORDER BY
clause and it will be fine.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

'ROW_NUMBER' is not a recognized function name.

I am getting the following error while excuting following query in sqlserver 2005:

SELECT ProductName, UnitPrice,

ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank

FROM Products

ORDER BY UnitPrice DESC

if any one know what should be done to avoid this, please let me know.

Thanks in advance,

Rajanikanth.

Check and see if the database is running in SQL Server 2000 compatibility mode. Try running these two commands:

Code Snippet

select @.@.version

exec sp_dbcmptlevel 'yourDatabaseName'

|||

As Kent stated, check that you are connecting to a SS 2005 server. You could be using the client tools shipped with 2005, but if you connect to a 2000 instance, for example, then you will not be able to use the new features from 2005. The db compatibility level does not limit you from using the new features, if it is hosted in a 2005 instance.

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

sql

Row Order on View Results

When I run a view on SQL 2005 the resulting rows are not in order, even if the SQL statement defining the view includes an order by clause.

Within the Microsoft SQL Manager Studio (SQL 2005), when the view is opened, the rows are in no specific order.

Records viewed remotely via ADO likewise are not displayed in order. Neither are records viewed via ODBC.

Interestingly, when opened in modify mode within the Microsoft SQL Manager Studio (SQL 2005), the view does display the records according to the ORDER BY clause.

On the other hand, the same view on SQL 2000 produces result sets organized according to the ORDER BY clause. This is true whether the view is opened normally or in design mode.

And records viewed remotely via ADO are displayed in order, as are records viewed via ODBC.

I find this disappointing and a stumbling block in moving databases out of SQL 2000 and into SQL 2005.

The Database that I used was one pulled into a SQL 2005 64 bit server out of a back up made by a SQL 2000 server of a SQL 2000 database.

In general it's not recommended to include an ORDER BY clause in a view. A view should define a new relation of attributes derived from existing attributes in the datamodel. A query using the view should apply an order by on the data represented by the view to produce an ordered resultset.

Row order

Hi,

I want to show rows order (row number) in my query result set.
How can I do?

Example:

SELECT GetRowNumber(), Field1 FROM MY_TABLE WHERE ...

GetRowNumber(), Field1

1 Value1
2 Value2
3 Value4
.....

GetRowNumber() is a sp, udf, anyway

If you are using SQL Server 2005 consider the ROW_NUMBER() feature; look this up in books online. It is a useful feature that is new to SQL Server 2005.|||

Here is an example using the row_number() function:

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY LastName, FirstName ),
FirstName,
LastName
FROM Employees

|||

I'm using SQL server 2000

|||

These resources should give you the help you need.

Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx

|||What is the purpose of the row number? Why do you need it? Is it local to the results returned from the query? If so, why don't you generate it on the client-side? It is much easier to do and consumes less resources. You can use ROW_NUMBER or temporary table with identity column or sub-query to generate sequence numbers. But performance will not be that good with any of these methods so it depends on why you need to generate it in the first place?|||

Arnie,

This info is very helpful..however, i do have another question. How do i set the data types into varchar (6) for the row number? for example:

row firstname lastname

000001 richie rich

000002 john anderson

000003 will smith

000004 amber white

.

.

.

.

000010 william smith

instead of:

row firstname lastname

1 richie rich

2 john anderson

3 will smith

4 amber white

.

.

.

.

.

10 william smith

|||

Something like this:

Code Snippet

SELECT right(( '000000' + cast( row as varchar(6))), 6 )

|||

Thanks Arnie,

I have another issue to discuss. I have two different data to be entered into one sql table. I have done the first one which has let say, 2000 records. the second data needs to be entered into the table right after the first one.

let's say,

First data comes from a table named Service,

Second data comes from a table named File.

both different tables need to be mapped into one table with a primary key created with row order.

I have mapped Service with primary key 1 until 2000. Now, i want to continue from 2001 for File.

How do i generate a primary key that would continue from what i have left sequentially?

Thanks in advance,

Jul.

|||

One option would be to have an IDENTITY column for the new table, and set the start value as 2001.

Another option would be to use the same type of numbering query as posted above, and add 2000 to the values. Something like this:

Code Snippet

USE Northwind
GO

SELECT
c1.ContactName,
Rank = ( COUNT(*) + 2000 )
FROM Customers c1
JOIN Customers c2
ON c2.ContactName <= c1.ContactName
GROUP BY c1.ContactName
ORDER BY c1.ContactName;

|||

It gives me an error:

Column 'FBSourceTable..Case.CaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||Please post your entire query.

Row order

Hi,

I want to show rows order (row number) in my query result set.
How can I do?

Example:

SELECT GetRowNumber(), Field1 FROM MY_TABLE WHERE ...

GetRowNumber(), Field1

1 Value1
2 Value2
3 Value4
.....

GetRowNumber() is a sp, udf, anyway

If you are using SQL Server 2005 consider the ROW_NUMBER() feature; look this up in books online. It is a useful feature that is new to SQL Server 2005.|||

Here is an example using the row_number() function:

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY LastName, FirstName ),
FirstName,
LastName
FROM Employees

|||

I'm using SQL server 2000

|||

These resources should give you the help you need.

Row Number (or Rank) from a SELECT Transact-SQL statement (includes Paging)
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx
http://www.projectdmx.com/tsql/ranking.aspx

|||What is the purpose of the row number? Why do you need it? Is it local to the results returned from the query? If so, why don't you generate it on the client-side? It is much easier to do and consumes less resources. You can use ROW_NUMBER or temporary table with identity column or sub-query to generate sequence numbers. But performance will not be that good with any of these methods so it depends on why you need to generate it in the first place?|||

Arnie,

This info is very helpful..however, i do have another question. How do i set the data types into varchar (6) for the row number? for example:

row firstname lastname

000001 richie rich

000002 john anderson

000003 will smith

000004 amber white

.

.

.

.

000010 william smith

instead of:

row firstname lastname

1 richie rich

2 john anderson

3 will smith

4 amber white

.

.

.

.

.

10 william smith

|||

Something like this:

Code Snippet

SELECT right(( '000000' + cast( row as varchar(6))), 6 )

|||

Thanks Arnie,

I have another issue to discuss. I have two different data to be entered into one sql table. I have done the first one which has let say, 2000 records. the second data needs to be entered into the table right after the first one.

let's say,

First data comes from a table named Service,

Second data comes from a table named File.

both different tables need to be mapped into one table with a primary key created with row order.

I have mapped Service with primary key 1 until 2000. Now, i want to continue from 2001 for File.

How do i generate a primary key that would continue from what i have left sequentially?

Thanks in advance,

Jul.

|||

One option would be to have an IDENTITY column for the new table, and set the start value as 2001.

Another option would be to use the same type of numbering query as posted above, and add 2000 to the values. Something like this:

Code Snippet

USE Northwind
GO

SELECT
c1.ContactName,
Rank = ( COUNT(*) + 2000 )
FROM Customers c1
JOIN Customers c2
ON c2.ContactName <= c1.ContactName
GROUP BY c1.ContactName
ORDER BY c1.ContactName;

|||

It gives me an error:

Column 'FBSourceTable..Case.CaseID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

|||Please post your entire query.

Row numbers in select statements

Is there an easy way to do get a row number in each row in a select
statement?
Something like:
select rownumber,description price from lineorder order by row number
Thanks,
TomTshad,
Yes.
See:
How to dynamically number rows in a SELECT statement.
http://support.microsoft.com/defaul...kb;en-us;186133
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||Why can't the presentation tier do this? It's the only place that HAS to
loop through each row, one by one. Now you're forcing the database to do it
too, and performance can only suffer for it.
http://www.aspfaq.com/2427
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eV$hgGZ2FHA.636@.TK2MSFTNGP10.phx.gbl...
> Tshad,
> Yes.
> See:
> How to dynamically number rows in a SELECT statement.
> http://support.microsoft.com/defaul...kb;en-us;186133
I tried that:
Select rank=count(*),
Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2 then
r.ItemName end as Description,
Price, PurchaseQty, TotalPrice = Price * PurchaseQty
from PurchaseDetail pd
join PurchaseMaster pm on (pd.PurchaseMasterID = pm.PurchaseMasterID)
left JOIN JobPostingPrices j on (ProductID = JobPostingPriceID)
left JOIN ResumeAccessPrices r on (ProductID = ResumeAccessPriceID)
where CompanyID = 153973
group by Case when ProductTypeID = 1 then j.ItemName when ProductTypeID = 2
then r.ItemName end,Price,PurchaseQty,Price * PurchaseQty
order by 1
But in my 8 rows returned I got (1,1,1,1,1,1,2,2)'
Is the Joins causing me a problem?
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OUJZsHZ2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Why can't the presentation tier do this? It's the only place that HAS to
> loop through each row, one by one. Now you're forcing the database to do
> it too, and performance can only suffer for it.
Actually, it can.
But that is an extra step,as I am binding it to a datagrid.
Tom
> http://www.aspfaq.com/2427
>
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
>|||If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
My philosophy is that rules of database normalization apply only to physical
tables and not to query results.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23H7LyDZ2FHA.2816@.tk2msftngp13.phx.gbl...
> Is there an easy way to do get a row number in each row in a select
> statement?
> Something like:
> select rownumber,description price from lineorder order by row number
> Thanks,
> Tom
>|||> My philosophy is that rules of database normalization apply only to
> physical tables and not to query results.
FWIW, my objection to doing this in the database has nothing to do with
normalization at all, but with the extra work required (whether using a
subquery, or copying all the data to a separate table first). A simple
counter at the presentation layer is the very least impact on performance,
since it has to loop through all rows and display them one by one anyway.
A|||This assumes that the result is being consumed in such a way that the
developer can add the additional computed column. It may be exported from
DTS to a table or file, executed only in Query Analyzer and pasted into
email, or bound to a datagrid.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utST6ik2FHA.1184@.TK2MSFTNGP12.phx.gbl...
> FWIW, my objection to doing this in the database has nothing to do with
> normalization at all, but with the extra work required (whether using a
> subquery, or copying all the data to a separate table first). A simple
> counter at the presentation layer is the very least impact on performance,
> since it has to loop through all rows and display them one by one anyway.
> A
>|||> This assumes that the result is being consumed in such a way that the
> developer can add the additional computed column. It may be exported from
> DTS to a table or file, executed only in Query Analyzer and pasted into
> email, or bound to a datagrid.
Yep, that's why I asked, "why can't the presentation tier do this?" and did
not say "ONLY the presentation tier can do this!"|||But do you think that only the presentation tier *should* do this?
;-)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OhisP5k2FHA.1572@.TK2MSFTNGP10.phx.gbl...
> Yep, that's why I asked, "why can't the presentation tier do this?" and
> did not say "ONLY the presentation tier can do this!"
>

Row numbering unpredictable

Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/Assuming CustomerName is unique:
select
(select count (*)
from #tmp t1
where t1.CustomerName <= t2.CustomerName) as rownum
, *
from
#tmp t2
order by
t2.CustomerName
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
Hi,
I need to create a stored procedure that returns the row number (for
paging) AFTER the data has been sorted with an order by. The source is
a view. The code I have is:
SELECT rownum = IDENTITY(1,1,bigint), *
INTO #tmp
FROM viewName
ORDER BY CustomerName -- field name I'm ordering by
When I recieve the results back, the rownum column is not the same
order as the customername (it jumps half way to a high number?!?),
which means I can't page it based on rownum without jumping all over
the dataset.
Anyone got any ideas on how to solve that other than client side paging
(in ADO :-P)
This is SQL 2000 SP3 (pah!)
Cheers,
Chris Smith
http://www.cswd.co.uk/|||you could create the table first with an ID column, then insert into
it. I suspect (though have no evidence) that the select into #tmp with
an id column created then is having issues with the order by|||(cseemeuk@.googlemail.com) writes:
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
people that it's guaranteed to work. Whether that really is true, I'm not
completely convinced of, but fairly. In any case, SELECT INTO is *not*
guaranteed to work that way, so stay away from it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks - works perfectly. The INTO was the problem - appears to be no
guaranteed order to the IDENTITY(bigint, 1,1)
All sorted
Cheers,
Chris Smith
http://www.cswd.co.uk/|||The order is not guaranteed when you use SELECT INTO.
See
http://support.microsoft.com/defaul...kb;en-us;273586
For a list of paging options see
http://www.aspfaq.com/show.asp?id=2120
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<cseemeuk@.googlemail.com> wrote in message
news:1144755017.455143.6760@.v46g2000cwv.googlegroups.com...
> Hi,
> I need to create a stored procedure that returns the row number (for
> paging) AFTER the data has been sorted with an order by. The source is
> a view. The code I have is:
> SELECT rownum = IDENTITY(1,1,bigint), *
> INTO #tmp
> FROM viewName
> ORDER BY CustomerName -- field name I'm ordering by
> When I recieve the results back, the rownum column is not the same
> order as the customername (it jumps half way to a high number?!?),
> which means I can't page it based on rownum without jumping all over
> the dataset.
> Anyone got any ideas on how to solve that other than client side paging
> (in ADO :-P)
> This is SQL 2000 SP3 (pah!)
> Cheers,
> Chris Smith
> http://www.cswd.co.uk/
>|||One would think this type of thing,so common and important,
would have a kb or something written by MS.Are you aware of any
link?If none exists I would ask you to kindly request something in
'writing'.Key points of an enterprise database should not be rattling
around just in someone head! :)
Clarity,clarity and nothing but clarity.
Regards from:
www.rac4sql.net
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A28BF07D031Yazorman@.127.0.0.1...
> (cseemeuk@.googlemail.com) writes:
> Create the table with CREATE TABLE, and then use INSERT with SELECT ORDER
> BY. Add OPTION (MAXDOP 1) as an extra precaution. I've been told from MS
> people that it's guaranteed to work. Whether that really is true, I'm not
> completely convinced of, but fairly. In any case, SELECT INTO is *not*
> guaranteed to work that way, so stay away from it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Steve Dassin wrote:
> One would think this type of thing,so common and important,
> would have a kb or something written by MS.Are you aware of any
> link?If none exists I would ask you to kindly request something in
> 'writing'.Key points of an enterprise database should not be rattling
> around just in someone head! :)
> Clarity,clarity and nothing but clarity.
http://support.microsoft.com/defaul...kb;en-us;273586
Do not assume that article means that all INSERTs will always cause
IDENTITY to be generated in a predetermined order. There are at least
some situations where that doesn't work - whether by design or a bug I
can't say.
Perhaps the safest course is to assume that you cannot control the
IDENTITY sequence with ORDER BY. In my view the wisest and most logical
solution is to use other methods like the ROW_NUMBER function for
example.
I can think of at least two good reasons for not using IDENTITY the way
proposed by the KB. Firstly IDENTITY is normally intended as an
arbitrary surrogate key - using the values in any "meaningful" way is a
compromise you don't need and is something it just isn't designed for.
Secondly, this supposed behaviour of an "ordered" INSERT looks contrary
to the set-based nature of an INSERT statement. Whether or not it works
today, it seems undesirable to assume that it should always work that
way in future. One would hope and expect that the engine could optimise
out any redundant sorting in INSERT...SELECT queries. That seems to be
what happens in some cases today and maybe it will happen more often in
future versions due to improvements in the optimiser. Just some things
to bear in mind.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||>> Anyone got any ideas on how to solve that other than client side paging <
<
The basic principle of a tiered architecture is that display is done in
the front end adn NEVER in the database. Why are you sing violating
40 years of Software Engineering?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1144803667.948395.290650@.i40g2000cwc.googlegroups.com...
<<
> The basic principle of a tiered architecture is that display is done in
> the front end adn NEVER in the database. Why are you sing violating
> 40 years of Software Engineering?
>
Forty years of a life sentence is enough.Time to let the innocent free.
Convicted on trumped up,unsubstantiated and false charges.In other words,
NONSENSE.
The thread:
Monday, April 10, 2006 9:48 PM
microsoft.public.sqlserver.programming
Re: Membership Timeline Spanning
contains a response that further clarifies things:
"Itzik Ben-Gan" writes
>.
>In my previous reply I mentioned the ANSI OVER clause (with an ORDER BY
>option). It is really brilliant, and I wonder if the designers of the
>feature themselves knew how profound it is. I believe this option to be the
>bridge between cursors and sets; sort of the holy grail of SQL. :-)
To quote Bob Dylan:
'I would not feel so alone if everyone where getting stoned':)
Yes I agree with you in principal.The 'real' paradign shift has
little to do with the clr and everything to do with exploding
the perverted myth of the exclusivity of'set based' constructs.
The idea one can legitimately think in terms of rows without being
labelled an sql Jodus has arrived.But calling this windowing a
'profound' kind of insight and bestowing on the designers the aura
of 'brilliance' would be a mistake.It is at best an example of
'better late than never'.Calling this state of affairs profound
would surely overshadow the accountability that the commericial
database world should be held to.The fact that this mindset change
has taken almost 30 years should be seen as appalling.Neo-cons of
the industry had hijacked sense with sql creationism and marketing.
WMD was replaced with client/server and a tiered approach.A theory
was misapplied to a retrival mechanism and unapplied to a design
mechanism.An approach that vendors marketted that allowed them to
hide both their intellectual and creative shortcomings.Their db
failures made for the 'client'.And now the clr in the db has replaced
the client.And of course the dreaded cursor.This demanded regime
change and the field was bankrupted for 30 years.For this we are to
praise Ceasar?I think not.
It is interesting to look at the fanfare that vendors are using
to usher in this new paradign.In their documentation Oracle refers
to their analytic functions in windows as an example of
'data densification'.This phrase is supposed to illustrate the
flip side of the Group By.It was obviously borrowed from the idea
of pacification,right out of the Pentagon.This is the best they could
come up with?Any army of engineers berefit of language and concepts.
Not to be out done,MS in its highly touted BOL offers the next best
thing - absolutely Nothing!No explanations,no history no seqways.
The functions are thrown around like so much spaghetti on a wall.
If you write about concepts someone may quote you.MS needn't worry
now.Least I be accused of favortism,IBM was too busy pleasing its
shareholders to write anything intelligible.
Finally,to your point about MS leaving out a large chunk of analytic
material this was obviously not an oversight but just insurance
that anything done with sql-99 could most definitly be easily ported
to the competition.Less is more.Please!If they weren't sure of
what they were doing they could have at least looked at Oracle
which is probably about 8 years ahead.Or even looked at RAC to see what
you and I are really talking about :)
Interested readers maybe surprised that many of the ideas in sql
analytics can be found in the SAS (Statistical Analysis System) Data
Step...introduced about 20 years ago!Many of the Oracle extensions
(First/Last) can also be found here.MySql allows mixing of variables
and columns in a SELECT.Most of the analytics can be easily simulated
in a single SELECT.And of course little RAC, way ahead of its time:)
Some musing from:
www.rac4sql.net

row number

update [Costing].[dbo].[Supplier]

set n =

(

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

where t.suppliercode = Supplier.suppliercode

)

hi i cannot update a column with the row number,

it is all taking 1001, supposed to be 1001,1002 and so on .

thanks.

Hi,

Use better something like this here:

update [Costing].[dbo].[Supplier]

set n = Subquery.RowNumber

FROM [Costing].[dbo].[Supplier] S

INNER JOIN (

SELECT (ROW_NUMBER() OVER (ORDER BY suppliercode) + 1000 )

as RowNumber from [Costing].[dbo].[Supplier] as t

) Subquery

ON Subquery.suppliercode = S.suppliercode

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

thanks it works , i did it shortcut way , not a good solution though.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER procedure [dbo].[DebugCursor]

as

BEGIN

SET NOCOUNT ON;

DECLARE cursorDebug Cursor FOR Select suppliercode

From supplier

Declare @.a nvarchar(25)

declare @.c int

set @.c = 300

Open cursorDebug Fetch NEXT FROM cursorDebug INTO @.a

While @.@.FETCH_STATUS =0

BEGIN

Update supplier Set n = @.c

Where suppliercode = @.a

set @.c = @.c + 1

Fetch NEXT FROM cursorDebug INTO @.a

END

CLOSE cursorDebug

DEALLOCATE cursorDebug

END

|||YOu should always prefer set based operations.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Row Lock

I have my password table and will like to lock one of the rows in order not to allow anyone change it since it is being used by my applications.
Please let me know.
Thanks...
Stoko
What do you mean that you have a password table?
Is this part of the sysxlogins? Did you create your own security tables?
Some additional information might be helpful.
On a side note, I don't think that there is a way to keep people from
working with an individual row in the table other than to use some type of
view that has permissions on it, or to create column level permissions on
the table itself.
Rick Sawtell
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> I have my password table and will like to lock one of the rows in order
not to allow anyone change it since it is being used by my applications.
> Please let me know.
> Thanks...
> Stoko
|||Rick,
This table is a userdefined table I created with logins/pwd fields. You may be right that I should do a column level lock than row level. How then is this done?
Thanks...Stoko
"Rick Sawtell" wrote:

> What do you mean that you have a password table?
> Is this part of the sysxlogins? Did you create your own security tables?
> Some additional information might be helpful.
> On a side note, I don't think that there is a way to keep people from
> working with an individual row in the table other than to use some type of
> view that has permissions on it, or to create column level permissions on
> the table itself.
> Rick Sawtell
>
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> not to allow anyone change it since it is being used by my applications.
>
>
|||Stoko,
How would a user update a password in this table? Can a user access the
table directly, or are they only able to do it via an application and you
are just trying to prevent a user from changing that password via that
screen?
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> Rick,
> This table is a userdefined table I created with logins/pwd fields. You
may be right that I should do a column level lock than row level. How then
is this done?[vbcol=seagreen]
> Thanks...Stoko
> "Rick Sawtell" wrote:
tables?[vbcol=seagreen]
of[vbcol=seagreen]
on[vbcol=seagreen]
order[vbcol=seagreen]
|||SQL is big time DB - meant for many, many users.
You should not have the client hold locks on the DB at all - imagine hundreds of users holding locks on PC's all around a WAN - what a nightmare.
You need to handle updates differently. Only allow an update if the row hasn't been changed since you read it. By using a datetime field - or checking that the value you are about to update hasn't changed.
Many different ways to handle this - but a major change in mind-set.
"slamm" wrote:

> Stoko,
> How would a user update a password in this table? Can a user access the
> table directly, or are they only able to do it via an application and you
> are just trying to prevent a user from changing that password via that
> screen?
>
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> may be right that I should do a column level lock than row level. How then
> is this done?
> tables?
> of
> on
> order
>
>

Row Lock

I have my password table and will like to lock one of the rows in order not
to allow anyone change it since it is being used by my applications.
Please let me know.
Thanks...
StokoWhat do you mean that you have a password table?
Is this part of the sysxlogins? Did you create your own security tables?
Some additional information might be helpful.
On a side note, I don't think that there is a way to keep people from
working with an individual row in the table other than to use some type of
view that has permissions on it, or to create column level permissions on
the table itself.
Rick Sawtell
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> I have my password table and will like to lock one of the rows in order
not to allow anyone change it since it is being used by my applications.
> Please let me know.
> Thanks...
> Stoko|||Rick,
This table is a userdefined table I created with logins/pwd fields. You may
be right that I should do a column level lock than row level. How then is
this done?
Thanks...Stoko
"Rick Sawtell" wrote:

> What do you mean that you have a password table?
> Is this part of the sysxlogins? Did you create your own security tables?
> Some additional information might be helpful.
> On a side note, I don't think that there is a way to keep people from
> working with an individual row in the table other than to use some type of
> view that has permissions on it, or to create column level permissions on
> the table itself.
> Rick Sawtell
>
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> not to allow anyone change it since it is being used by my applications.
>
>|||Stoko,
How would a user update a password in this table? Can a user access the
table directly, or are they only able to do it via an application and you
are just trying to prevent a user from changing that password via that
screen?
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> Rick,
> This table is a userdefined table I created with logins/pwd fields. You
may be right that I should do a column level lock than row level. How then
is this done?[vbcol=seagreen]
> Thanks...Stoko
> "Rick Sawtell" wrote:
>
tables?[vbcol=seagreen]
of[vbcol=seagreen]
on[vbcol=seagreen]
order[vbcol=seagreen]|||SQL is big time DB - meant for many, many users.
You should not have the client hold locks on the DB at all - imagine hundred
s of users holding locks on PC's all around a WAN - what a nightmare.
You need to handle updates differently. Only allow an update if the row has
n't been changed since you read it. By using a datetime field - or checking
that the value you are about to update hasn't changed.
Many different ways to handle this - but a major change in mind-set.
"slamm" wrote:

> Stoko,
> How would a user update a password in this table? Can a user access the
> table directly, or are they only able to do it via an application and you
> are just trying to prevent a user from changing that password via that
> screen?
>
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> may be right that I should do a column level lock than row level. How the
n
> is this done?
> tables?
> of
> on
> order
>
>

Row Lock

I have my password table and will like to lock one of the rows in order not to allow anyone change it since it is being used by my applications.
Please let me know.
Thanks...
StokoWhat do you mean that you have a password table?
Is this part of the sysxlogins? Did you create your own security tables?
Some additional information might be helpful.
On a side note, I don't think that there is a way to keep people from
working with an individual row in the table other than to use some type of
view that has permissions on it, or to create column level permissions on
the table itself.
Rick Sawtell
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> I have my password table and will like to lock one of the rows in order
not to allow anyone change it since it is being used by my applications.
> Please let me know.
> Thanks...
> Stoko|||Stoko,
How would a user update a password in this table? Can a user access the
table directly, or are they only able to do it via an application and you
are just trying to prevent a user from changing that password via that
screen?
"stoko" <stoko@.discussions.microsoft.com> wrote in message
news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> Rick,
> This table is a userdefined table I created with logins/pwd fields. You
may be right that I should do a column level lock than row level. How then
is this done?
> Thanks...Stoko
> "Rick Sawtell" wrote:
> > What do you mean that you have a password table?
> >
> > Is this part of the sysxlogins? Did you create your own security
tables?
> >
> > Some additional information might be helpful.
> >
> > On a side note, I don't think that there is a way to keep people from
> > working with an individual row in the table other than to use some type
of
> > view that has permissions on it, or to create column level permissions
on
> > the table itself.
> >
> > Rick Sawtell
> >
> >
> > "stoko" <stoko@.discussions.microsoft.com> wrote in message
> > news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> > > I have my password table and will like to lock one of the rows in
order
> > not to allow anyone change it since it is being used by my applications.
> > >
> > > Please let me know.
> > >
> > > Thanks...
> > > Stoko
> >
> >
> >|||SQL is big time DB - meant for many, many users.
You should not have the client hold locks on the DB at all - imagine hundreds of users holding locks on PC's all around a WAN - what a nightmare.
You need to handle updates differently. Only allow an update if the row hasn't been changed since you read it. By using a datetime field - or checking that the value you are about to update hasn't changed.
Many different ways to handle this - but a major change in mind-set.
"slamm" wrote:
> Stoko,
> How would a user update a password in this table? Can a user access the
> table directly, or are they only able to do it via an application and you
> are just trying to prevent a user from changing that password via that
> screen?
>
> "stoko" <stoko@.discussions.microsoft.com> wrote in message
> news:1461F42D-921F-4F41-8CB3-38EDEAD7FB4D@.microsoft.com...
> > Rick,
> > This table is a userdefined table I created with logins/pwd fields. You
> may be right that I should do a column level lock than row level. How then
> is this done?
> >
> > Thanks...Stoko
> >
> > "Rick Sawtell" wrote:
> >
> > > What do you mean that you have a password table?
> > >
> > > Is this part of the sysxlogins? Did you create your own security
> tables?
> > >
> > > Some additional information might be helpful.
> > >
> > > On a side note, I don't think that there is a way to keep people from
> > > working with an individual row in the table other than to use some type
> of
> > > view that has permissions on it, or to create column level permissions
> on
> > > the table itself.
> > >
> > > Rick Sawtell
> > >
> > >
> > > "stoko" <stoko@.discussions.microsoft.com> wrote in message
> > > news:7461A023-256D-4449-8EFF-49F5EF6F74F1@.microsoft.com...
> > > > I have my password table and will like to lock one of the rows in
> order
> > > not to allow anyone change it since it is being used by my applications.
> > > >
> > > > Please let me know.
> > > >
> > > > Thanks...
> > > > Stoko
> > >
> > >
> > >
>
>

ROW IN A SPESIFIC ORDER

I HAVE FOUR ROWS IN A TABLE & I WANT THEM TO APPEARS IN A SPESIFIC ORDER AND I DON'T WANT TO SORT THEM.
please help meCan you give more datails?
You don't want sort them by SQL sort by clause?
In what kind of order?|||Exp:
SQL Sort LIKE

ASAP

C.F.E.D

Others

Zama

And I want to Sort Like
C.F.E.D
ASAP
ZAMA
OTHERS|||

You'll have to add some additional column to your table, perhaps called SortOrder. Then assign values to it:

SortOrder Value

10 C.F.E.D
20 ASAP
30 ZAMA
40 OTHERS

Then you can ORDER BY SortOrder.

|||but i don't want to show SortOrder. What I have to do for hiding this colunm.

Another Question is that I want to show "pie chart " Data label with percantage and as well as the name of the states
Like

Newyork, Calefornia
(5%) (30%)

Route optimization

Hi,

I am trying to generate scripts for route optimization, that is in what order a machine should operate on different sites with lowest cost of transportation.

I alreday have generated the matrix with distances between all sites.

And the problem now is how to generate the lists of all possible routes.

That is all possible combinations of in which order the sites can be operated.

Does anyone have a clue?

Thank you in advance

Sten-Gunnar

Sten-Gunnar:

This is one of the "classic" computer science problems; try looking up the "Traveling Salesman Problem". If you typically do not have more than a few deliveries to complete you should be able to "brute force" this problem with recursion in SQL Server 2005. This is interesting enough that a mock-up of this might be fun. The main word of caution for this problem is that this problem is viewed being of order N! (n factorial) -- basically of exponential order; therefore, if you are trying to solve this for "many" destinations I don't expect you to find a good solution.

Dave

|||

Look up "traveling salesman problem" or "traveling salesperson problem"

If there are N sites, and every pair of sites is a finite distance

apart, there are N! routes, which is a very large number for N bigger

than 10 or 12. For 20 sites, there are 2,432,902,008,176,640,000 routes,

and I doubt you want to try to enumerate all of them.

You will find a great deal of information on this problem if you search

for it.

-- Steve Kass

-- Drew University

-- http://www.stevekass.com

Sten-Gunnar@.discussions.microsoft.com wrote:

> Hi,

>

> I am trying to generate scripts for route optimization, that is in what

> order a machine should operate on different sites with lowest cost of

> transportation.

>

> I alreday have generated the matrix with distances between all sites.

>

> And the problem now is how to generate the lists of all possible routes.

> That is all possible combinations of in which order the sites can be

> operated.

>

> Does anyone have a clue?

>

> Thank you in advance

>

> Sten-Gunnar

>

>

|||

Thanks Dave and Steve.

What I have found so far in terms of code is this

http://technology.amis.nl/blog/?p=1221

Sten-Gunnar