Rearrange column

Hi!
I want to rearrange a column in a table within a stored procedure.
I got this table with this sample data:
ID Text Number
1 Testing 1
2 Testing2 2
3 Testing3 3
4 Testing4 4
5 Testing5 5
I want this:
ID Text Number
1 Testing 5
2 Testing2 4
3 Testing3 3
4 Testing4 2
5 Testing5 1
The numbers of rows varies so it must be dynamical and i want 2 inparameters
(startID and endID).
I hope someone can help me.
//MagnusNumber = endID - Number +1?
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:777484C3-3018-48ED-81D8-5E9BF071DD1C@.microsoft.com...
> Hi!
> I want to rearrange a column in a table within a stored procedure.
> I got this table with this sample data:
> ID Text Number
> 1 Testing 1
> 2 Testing2 2
> 3 Testing3 3
> 4 Testing4 4
> 5 Testing5 5
> I want this:
> ID Text Number
> 1 Testing 5
> 2 Testing2 4
> 3 Testing3 3
> 4 Testing4 2
> 5 Testing5 1
> The numbers of rows varies so it must be dynamical and i want 2
> inparameters
> (startID and endID).
> I hope someone can help me.
> //Magnus
>|||When you say "rearrange a column", exactly what transformation are you
looking for? If you only specify start and end rows then what
determines the order of the other values you don't specify?
If this column is always to be dynamic then it doesn't really make
sense to have it in the table at all. Derive it in a query like this:
SELECT A.au_id, A.au_lname,
(SELECT COUNT(*)
FROM pubs.dbo.authors
WHERE au_id<=A.au_id) AS number
FROM pubs.dbo.authors AS A
ORDER BY A.au_id ;
David Portas
SQL Server MVP
--|||The meaning with the column "Number" is to display data sorted in webform.
But sometime this displayorder must be changed.
The rest of the columns must stay the same.
//Magnus
"David Portas" wrote:

> When you say "rearrange a column", exactly what transformation are you
> looking for? If you only specify start and end rows then what
> determines the order of the other values you don't specify?
> If this column is always to be dynamic then it doesn't really make
> sense to have it in the table at all. Derive it in a query like this:
> SELECT A.au_id, A.au_lname,
> (SELECT COUNT(*)
> FROM pubs.dbo.authors
> WHERE au_id<=A.au_id) AS number
> FROM pubs.dbo.authors AS A
> ORDER BY A.au_id ;
> --
> David Portas
> SQL Server MVP
> --
>|||Mange
create table #test
(
col int not null primary key,
col1 char(1)
)
insert into #test values (1,'a')
insert into #test values (2,'b')
insert into #test values (3,'c')
insert into #test values (4,'d')
select col,col1,
(select count(*) from #test t where t.col>=#test.col) from #test
"Mange" <Mange@.discussions.microsoft.com> wrote in message
news:5ABD8DA6-F591-4EFD-8C19-716042AB7010@.microsoft.com...
> The meaning with the column "Number" is to display data sorted in webform.
> But sometime this displayorder must be changed.
> The rest of the columns must stay the same.
> //Magnus
>
>
> "David Portas" wrote:
>|||>> The meaning with the column "Number" is to display data sorted in webform
. But sometime this displayorder must be changed. The rest of the columns mu
st stay the same. <<
Instead of the vague name "number", you should have used sometrhng like
"physical_display_position" so people maintaining the code would know
it is not a logical data element.
The basic principle of a tiered architecture is that display is done in
the front end and never in the back end. This a more basic programming
principle than just SQL and RDBMS.|||It doesnt matter what the intention with the column is.
Do you mean that all sorting is done in the webform ?
"--CELKO--" wrote:

> Instead of the vague name "number", you should have used sometrhng like
> "physical_display_position" so people maintaining the code would know
> it is not a logical data element.
> The basic principle of a tiered architecture is that display is done in
> the front end and never in the back end. This a more basic programming
> principle than just SQL and RDBMS.
>