Order in the middle of a table

Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!You could easily write a stored proc to do this. One of the many
wonderful things about the UPDATE statement in the SQL language is that
it is an all-at-once operation. You can increment the vieworder for a
row and either increment or decrement its neighbour as appropriate all
at the same time. Try this batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just
call the stored proc with the appropriate parameters (a direction to
shuffle and a baseline row to shuffle along with its neighbour) from
your webpage using ADO (with a postback to reload the same webpage I guess).
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arjen wrote:

>Hi,
>I publish a table on a website. At the end of each row I want to show an up
-
>and downbutton. The buttons haves an id as parameter. Inside my table I hav
e
>a vieworder column, this is the way I sort the table.
>What I want is that when you press on the up- or downbutton that record
>moves up or down. This means an update of that record and the record above
>or below. I want to know what is the best way how to realize this.
>Ideas? Are there (build-in) funtions for this?
>Thank!
>
>|||Okay, thanks!
I will try this.
Arjen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> schreef in bericht news:e
yGZJViqFHA.564@.TK2MSFTNGP10.phx.gbl...
You could easily write a stored proc to do this. One of the many wonderful
things about the UPDATE statement in the SQL language is that it is an all-a
t-once operation. You can increment the vieworder for a row and either incr
ement or decrement its neighbour as appropriate all at the same time. Try t
his batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just call th
e stored proc with the appropriate parameters (a direction to shuffle and a
baseline row to shuffle along with its neighbour) from your webpage using AD
O (with a postback to reload the same webpage I guess).
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Arjen wrote:
Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!

order by

There is a field called field1 in a table called table1
Field1 is varchar(50)
Data inside field1 is such as:

3T
apr 2007
feb 2001
jan 2001
dec 1999
...

I am writing a sql query to return the data so that the field1 is sorted:
ie:

3T
dec 1999
jan 2001
feb 2001
apr 2007
...

This is what I have in the order by clause:

cast('01-' + replace(field1, ' ', '-') as datetime)

The problem is if there is something else such as '3T' or another text inside this field1.
Should there be a case statement inside the oprder by clause?

Please note that if there is any text which does not seem to be a date then it should appear first in the list as shown in the example above.
Thanks

Code Snippet

select'3T'as data

into #t

union allselect'feb 2001'

union allselect'apr 2007'

union allselect'dec 1999'

union allselect'jan 2001'

selectcaseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

from #t

orderbyisdate(data),

caseisdate(data)when 1 thenconvert(varchar(10),cast(data asdatetime), 102)else data end

|||

Use the following query..

Code Snippet

Create Table #data (

[Col] Varchar(100)

);

Insert Into #data Values('apr 2007');

Insert Into #data Values('feb 2001');

Insert Into #data Values('jan 2001');

Insert Into #data Values('dec 1999');

Insert Into #data Values('Americas');

Insert Into #data Values('APJ');

Insert Into #data Values('EMEA');

Insert Into #data Values('1982');

Insert Into #data Values('AJP09837');

Select * From #data Order By

Case When Isdate(Col)=1 Then 1 End,

Case When IsDate(Col)=1 Then cast(Col as datetime) End,

Col

drop table #data

ORACLE PROCEDURE VS. MS SQL SERVER PROCEDURE URGENT!

hi Guys !!
I hope you can help with this one...
I have to make a Procedure that can be able to receive and Array and process
inside itself..
In oracle you can declare a TABLE_TYPE and then you pass and array to it..
But how can i do that in MS SQL SERVER...?
I'll be really thanksfully
greetings
RemiHi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>|||Try looking into SQL_VARIANT data type.
Sincerely,
Anthony Thomas
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uv9l2VN5EHA.1192@.tk2msftngp13.phx.gbl...
Hi
There is no direct equivalent in SQL Server 2000 as your table type can have
different datatypes of each element. You may want to look at passing a
cursor or using a temporary table, alternatively it may be possible to stuff
(overlay) them into a character or binary variable. The following will be
useful reading: http://www.sommarskog.se/arrays-in-sql.html
John
"Remi" <Remi1@.hotmail.com> wrote in message
news:elM0x3M5EHA.2804@.TK2MSFTNGP15.phx.gbl...
> hi Guys !!
> I hope you can help with this one...
> I have to make a Procedure that can be able to receive and Array and
> process
> inside itself..
> In oracle you can declare a TABLE_TYPE and then you pass and array to it..
> But how can i do that in MS SQL SERVER...?
> I'll be really thanksfully
> greetings
> Remi
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craig
To use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally us
ed
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Exec
ute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally
used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>

Oracle Linked Servers

I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
Server 2000. Linking seems to work well. Then I fire off a few queries via
Query Analyzer at a table with ~3000 rows of data, and get the following
responses . . .
select top 1000 * from table - executes in 1 second
select top 2000 * from table - executes in 1 second
select top 3000 * from table - executes in 1 second
select top 4000 * from table - runs for > 5 minutes
select * from table - runs for > 5 minutes
Some additional comments on my setup -
1. I created a blank database in sql server
2. I created a set of views in sql server, one view for each table in
oracle, with a "select * from [oracle table]" for each view (acutally used
correct syntax to pull oracle data - above select is hypothetical ;-)
3. I have a few reporting views in sql server that aggregate base views; but
that is the extent of view nesting, 2 deep
Any comments? I was originally wanting to write things off to RAM, but I'm
wondering if there are issues with the ODBC / translation of the termination
of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
databases. When I try the Oracle OLE DB Provider, the tables show up in the
linked server but any Query Analyzer queries agains them error out with the
following:
Server: Msg 7320, Level 16, State 2, Line 2
Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute
returned 0x80040155].
Thanks for any help.
-craigTo use distributed transactions with the Oracle OLE DB Provider in your
environment you'll need to install the Oracle Service for MTS.
--
Michael D. Long
Microsoft MVP - Windows SDK
"Craig" <anonymous@.microsoft.com> wrote in message
news:e4p3OsDkEHA.3968@.TK2MSFTNGP11.phx.gbl...
> I've got an Oralce 8i db that I'm running as a Linked Server inside SQL
> Server 2000. Linking seems to work well. Then I fire off a few queries
via
> Query Analyzer at a table with ~3000 rows of data, and get the following
> responses . . .
> select top 1000 * from table - executes in 1 second
> select top 2000 * from table - executes in 1 second
> select top 3000 * from table - executes in 1 second
> select top 4000 * from table - runs for > 5 minutes
> select * from table - runs for > 5 minutes
> Some additional comments on my setup -
> 1. I created a blank database in sql server
> 2. I created a set of views in sql server, one view for each table in
> oracle, with a "select * from [oracle table]" for each view (acutally used
> correct syntax to pull oracle data - above select is hypothetical ;-)
> 3. I have a few reporting views in sql server that aggregate base views;
but
> that is the extent of view nesting, 2 deep
> Any comments? I was originally wanting to write things off to RAM, but
I'm
> wondering if there are issues with the ODBC / translation of the
termination
> of data. I'm using the Microsoft OLE DB Provider for Oracle to link the
> databases. When I try the Oracle OLE DB Provider, the tables show up in
the
> linked server but any Query Analyzer queries agains them error out with
the
> following:
> Server: Msg 7320, Level 16, State 2, Line 2
> Could not execute query against OLE DB provider 'OraOLEDB.Oracle'.
> OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ICommandText::Execute
> returned 0x80040155].
> Thanks for any help.
> -craig
>