help with an update

Table A
--
Listing_Number | Sort_Order
=====================
2000 | 1
2000 | 3
2000 | 4
2000 | 6
2000 | 9
I want the Sort_Order ordered like 1,2,3,4,5,6 etc. for every
listing_number. Could someone help with this problem ASAP. This is SQL
Server 2000.
Thank you,
ShivaWhat is this sort order, is it a autogenerated column or something you selec
t.
You need to give more information Shiva. Where are 3 and 5 that you want in
the sort order.
"Shiva" wrote:

> Table A
> --
> Listing_Number | Sort_Order
> =====================
> 2000 | 1
> 2000 | 3
> 2000 | 4
> 2000 | 6
> 2000 | 9
> I want the Sort_Order ordered like 1,2,3,4,5,6 etc. for every
> listing_number. Could someone help with this problem ASAP. This is SQL
> Server 2000.
> Thank you,
> Shiva
>
>|||Sort_Order should be always in sequence without losing a digit in between.
So the sequence should be 1,2,3, etc for a listing_number. So how can i
re-order the the sort_order in the table i have below?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:4D64DA4E-29CF-4549-8B84-C0783D2DF756@.microsoft.com...
> What is this sort order, is it a autogenerated column or something you
> select.
> You need to give more information Shiva. Where are 3 and 5 that you want
> in
> the sort order.
> "Shiva" wrote:
>|||Shiva
declare @.j int
set @.j=0
update yourtable
set @.j=i=@.j+1
"Shiva" <arbitsquare@.hotmail.com> wrote in message
news:u90lLIXfGHA.4932@.TK2MSFTNGP03.phx.gbl...
> Sort_Order should be always in sequence without losing a digit in between.
> So the sequence should be 1,2,3, etc for a listing_number. So how can i
> re-order the the sort_order in the table i have below?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:4D64DA4E-29CF-4549-8B84-C0783D2DF756@.microsoft.com...
>|||Sorry , "i" is yout List_Order column
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O69CJaXfGHA.1204@.TK2MSFTNGP02.phx.gbl...
> Shiva
> declare @.j int
> set @.j=0
> update yourtable
> set @.j=i=@.j+1
>
> "Shiva" <arbitsquare@.hotmail.com> wrote in message
> news:u90lLIXfGHA.4932@.TK2MSFTNGP03.phx.gbl...
>|||This won't work Uri. He wanted it reset for every listing number.
Btw, Shiva,what is the primary for this table?
"Uri Dimant" wrote:

> Sorry , "i" is yout List_Order column
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O69CJaXfGHA.1204@.TK2MSFTNGP02.phx.gbl...
>
>|||The table and the data already exists Uri.. He needs just the query :)
Anyways.. If the primary key is Listing_number,sort_order
then maybe we can try this..
declare @.a int, @.b int
set @.b = 0
set @.a = 0
update tableA set
@.a = Sort_Order = case when @.b = Listing_Number then @.a + 1 else 1 end,
@.b = Listing_Number|||Well, it is easy to "convert" SELECT to the UPDATE and there is no need to
declare variables
update #test set Sort_Order=(select count(*)from #test t where
t.Sort_Order<=#test.Sort_Order and
t.Listing_Number=#test.Listing_Number)
where exists (select *from #test t where
t.Sort_Order<=#test.Sort_Order and
t.Listing_Number=#test.Listing_Number)
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:C748F79B-DE79-42FB-872C-48028E79179F@.microsoft.com...
> The table and the data already exists Uri.. He needs just the query :)
> Anyways.. If the primary key is Listing_number,sort_order
> then maybe we can try this..
> declare @.a int, @.b int
> set @.b = 0
> set @.a = 0
> update tableA set
> @.a = Sort_Order = case when @.b = Listing_Number then @.a + 1 else 1 end,
> @.b = Listing_Number|||the previous update query you suggested was fine.
But why did you give an exists clause there?
what is the use of it?|||Hehehehehe, it is a habit to me , always put a WHERE condition , especially
for those requestes where people have not provided FULL ddl +primary
keys...
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:902CCA11-E48A-46A0-A35D-BEA8594CD79E@.microsoft.com...
> the previous update query you suggested was fine.
> But why did you give an exists clause there?
> what is the use of it?sql