Update column with min value from another tow / column

[bmacbmac]

Hi!

I have the following data:

create table #temp (counter int, bookpage varchar(10), pages int, imagename varchar(25), mincounter int)
insert into #temp (counter, bookpage, pages, imagename) values (1, '8/621', 1, '00000001.tif')
insert into #temp (counter, bookpage, pages, imagename) values (2, '8/621', 2, '00000002.tif')
insert into #temp (counter, bookpage, pages, imagename) values (3, '8/621', 3, '00000003.tif')
insert into #temp (counter, bookpage, pages, imagename) values (4, '8/624', 1, '00000004.tif')
insert into #temp (counter, bookpage, pages, imagename) values (5, '8/625', 1, '00000005.tif')
insert into #temp (counter, bookpage, pages, imagename) values (6, '8/625', 2, '00000006.tif')
insert into #temp (counter, bookpage, pages, imagename) values (7, '8/626', 1, '00000007.tif')
insert into #temp (counter, bookpage, pages, imagename) values (8, '8/626', 2, '00000008.tif')
insert into #temp (counter, bookpage, pages, imagename) values (9, '8/626', 3, '00000009.tif')
insert into #temp (counter, bookpage, pages, imagename) values (10, '8/626', 4, '00000010.tif')
insert into #temp (counter, bookpage, pages, imagename) values (11, '8/627', 1, '00000011.tif')
insert into #temp (counter, bookpage, pages, imagename) values (12, '8/627', 2, '00000012.tif')

Each group has has 'pages' that begin numbering at 1 and then moves up. For example, 8/621 begins at page one and goes through page 3. The next group (8/624) is just a one page group. The next group (8/625 is 2 pages).

I am hoping to update the mincounter (for each group) to equal the page 1 counter for each group. Basically, anywhere there is a '2', set the mincounter to it's preceding 1. Same with 3, 4, 5, etc.

For example, something like this:
counter, bookpage, pages, imagename, mincounter
1, 8/621, 1, 00000001.tif, 1
2, 8/621, 2, 00000002.tif, 1
3, 8/621, 3, 00000003.tif, 1
4, 8/624, 1, 00000004.tif, 4
5, 8/625, 1, 00000005.tif, 5
6, 8/625, 2, 00000006.tif, 5
7, 8/626, 1, 00000007.tif, 7
8, 8/626, 2, 00000008.tif, 7
9, 8/626, 3, 00000009.tif, 7
10, 8/626,4, 00000010.tif, 7
11, 8/627, 1, 00000011.tif, 11
12, 8/627, 2, 00000012.tif, 11

Can someone help me with this?

 

[imex]

Hi,

Try something like this:

with CTE_RK as
(
    select
        *,
        RANK() OVER(ORDER BY bookpage) as RK
    from MyTable
)

update CTE_RK
set mincounter = RK

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[TamarGranor]

I think your use of the word "counter" as a fieldname confused Imex. I think what you want is:

WITH cteFirstPage AS 
  (SELECT BookPage, Min(Counter) AS MinCounter
     FROM #temp 
     GROUP BY BookPage)

UPDATE #temp
   SET MinCounter = cteFirstPage.MinCounter
   WHERE BookPage = cteFirstPage.BookPage

Tamar

 

[bmacbmac]

Thanks Tamar... yes I see now how that could be confusing... I should name that a little differently.

I like where you are going with that script, although when I run it I get an error:

Msg 4104, Level 16, State 1, Line 8
The multi-part identifier "cteFirstPage.BookPage" could not be bound.

 

[bmacbmac]

Tamar,

I changed the script to this:

WITH cteFirstPage AS 
  (SELECT BookPage as bookpage, Min(Counter) AS MinCounter
     FROM #temp 
     GROUP BY BookPage)
     
     --select cteFirstPage.bookpage from ctefirstpage

UPDATE #temp
   SET MinCounter = cteFirstPage.MinCounter
   from #temp
  join ctefirstpage on #temp.bookpage = ctefirstpage.bookpage

and it seems to run great!

 

[TamarGranor]

Glad I was able to get you on the right path.

Tamar