1

Topic: To walk on lines of the table and to compare values from one column.

Good afternoon, prompt what to use for the decision of such task:
Let's assume to eat the table
ID THE NAME THE SURNAME THE POST
1 Vasja Ignatov the chief
1 Vasja Ignatov the cleaner
1 Vasja Ignatov the bookkeeper

5 Lena Karpova the cleaner
8 Nina Sidorov the bookkeeper
8 Nina Sidorov the seller

It is necessary for me for everyone identical ID to walk on posts, to see that the chief is its higher post and  instead of the cleaner - the chief and instead of the bookkeeper - the chief.
I.e. That it turned out so:
ID THE NAME THE SURNAME THE POST
1 Vasja Ignatov the chief
1 Vasja Ignatov the chief
1 Vasja Ignatov the chief

5 Lena Karpova the cleaner
8 Nina Sidorov the bookkeeper
8 Nina Sidorov the bookkeeper

For convenience of comparing, perhaps I can assign instead of a post title number, for example
ID THE NAME THE SURNAME THE POST
1 Vasja Ignatov 9
1 Vasja Ignatov 2
1 Vasja Ignatov 5

5 Lena Karpova 2
8 Nina Sidorov 5
8 Nina Sidorov 4

And to look if 9> 2, to appropriate 9.
But how to transit these lines? How generally arrive, if it is necessary to compare periods?

2

Re: To walk on lines of the table and to compare values from one column.

Taya_1993;
If

Taya_1993 wrote:

For convenience of comparing, perhaps I can assign instead of a post title number

That

WITH [cte] ([ID], [THE NAME THE SURNAME], [THE POST])
AS (
SELECT TOP 1 WITH TIES
[t]. [ID]
, [t]. [THE NAME THE SURNAME]
, [t]. [POST]
FROM [we Assume to eat the table] [t]
ORDER BY ROW_NUMBER () OVER (PARTITION BY [t]. [ID], [t]. [THE NAME THE SURNAME] ORDER BY [t]. [THE POST] DESC) ASC
)
UPDATE [t]
SET [POST] = [cte]. [POST]
FROM [we Assume to eat the table] [t]
INNER JOIN [cte] ON [cte]. [ID] = [t]. [ID]
AND [cte]. [THE NAME THE SURNAME] = [t]. [THE NAME THE SURNAME]
;

3

Re: To walk on lines of the table and to compare values from one column.

Guf;
And that not max () over (partition)?
Then and  in an update it is not required...

4

Re: To walk on lines of the table and to compare values from one column.

Kind  - Eh;
And I till now in 2008 live, to us such yet did not deliver sad((

5

Re: To walk on lines of the table and to compare values from one column.

Guf wrote:

Kind  - Eh;
And I till now in 2008 live, to us such yet did not deliver sad((

I say lies! Delivered! I am corrected.

WITH [cte] [THE POST OLD], [THE POST NEW])
AS (
SELECT TOP 1 WITH TIES
, [t]. [POST]
, MAX () OVER (PARTITION BY [t]. [THE POST])
FROM [we Assume to eat the table] [t]
)
UPDATE [cte]
SET [THE POST OLD] = [THE POST NEW]
;

6

Re: To walk on lines of the table and to compare values from one column.

Guf;
top 1 with ties, probably, the superfluous? wink

7

Re: To walk on lines of the table and to compare values from one column.

Kind  - Eh;
I as iap smile))

WITH [cte] [THE POST OLD], [THE POST NEW])
AS (
SELECT [t]. [POST]
, MAX () OVER (PARTITION BY [t]. [THE POST])
FROM [we Assume to eat the table] [t]
)
UPDATE [cte]
SET [THE POST OLD] = [THE POST NEW]
;

8

Re: To walk on lines of the table and to compare values from one column.

Taya_1993;
WITH TB AS
(
SELECT 1 ID, ' Vasja Ignatov ' FIO, 9 PROF
UNION ALL
SELECT 1 ID, ' Vasja Ignatov ' FIO, 2 PROF
UNION ALL
SELECT 1 ID, ' Vasja Ignatov ' FIO, 5 PROF
UNION ALL
SELECT 5 ID, ' Lena Karpova ' FIO, 2 PROF
UNION ALL
SELECT 8 ID, ' Nina Sidorov ' FIO, 5 PROF
UNION ALL
SELECT 8 ID, ' Nina Sidorov ' FIO, 4 PROF
)
SELECT *, PROF_PRIOR = MAX (PROF) OVER (PARTITION BY (ID))
FROM TB

9

Re: To walk on lines of the table and to compare values from one column.

Thanks everything, very much helped smile