Most used text

Transact-SQL
1

I would like to update a field (yearclass) for each row with the most common text in payclass for each grower, block, section and year combination.

In the case where there are equal amounts of payclass, weight should be used to determine yearclass. Highest sum of weight wins.

I have tried many queries of which none worked.

My schema is

USE [GotoVine]
GO
/****** Object: Table [dbo].[wbridge_historyBasies] Script Date: 17/10/2019 09:55:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[wbridge_historyBasies](
[ID] [int] IDENTITY(1,1) NOT NULL,
[grower] varchar NULL,
[block] varchar NULL,
[section] varchar NULL,
[year] [int] NULL,
[weight] [decimal](5, 2) NULL,
[payclass] varchar NULL,
[yearclass] varchar NULL,
CONSTRAINT [PK_wbridge_historyBasies] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[wbridge_historyBasies] ON

My data

INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (1, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(4.94 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (2, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(4.00 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (3, N'2542-WIG', N'53057', N'12-WEL', 2013, CAST(5.46 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (4, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(5.78 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (5, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.32 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (6, N'2437-VIS', N'53067', N'14', 2013, CAST(5.96 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (7, N'2437-VIS', N'53067', N'14', 2013, CAST(5.50 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (8, N'2437-VIS', N'53067', N'14', 2013, CAST(5.36 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (9, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.26 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (10, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.56 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (11, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(6.52 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (12, N'1295-COR', N'53058', N'12-NUW', 2013, CAST(5.26 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (13, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(4.56 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (14, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(4.83 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (15, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(3.88 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (16, N'2542-WIG', N'53057', N'12-WEL', 2014, CAST(5.14 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (17, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(5.40 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (18, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(4.12 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (19, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(5.04 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (20, N'2542-WIG', N'53057', N'12-WEL', 2015, CAST(4.66 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (21, N'2542-WIG', N'53057', N'12-WEL', 2016, CAST(4.80 AS Decimal(5, 2)), N'4-BULK', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (22, N'2542-WIG', N'53057', N'12-WEL', 2016, CAST(4.76 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (23, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(4.52 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (24, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(5.02 AS Decimal(5, 2)), N'3-KULT', NULL)
INSERT [dbo].[wbridge_historyBasies] ([ID], [grower], [block], [section], [year], [weight], [payclass], [yearclass]) VALUES (25, N'2542-WIG', N'53057', N'12-WEL', 2017, CAST(5.48 AS Decimal(5, 2)), N'6-DROWIT', NULL)
SET IDENTITY_INSERT [dbo].[wbridge_historyBasies] OFF

The required result (I include a picture of an Excel file - I cannot see how to attach a file to this post)

Result
Result572×629 123 KB

Regards and thank you in advance.

2 
UPDATE whB
SET yearclass = top_payclasses.payclass
FROM dbo.wbridge_historyBasies whB
INNER JOIN (
    SELECT grower, block, section, year, payclass,
        --COUNT(*) AS payclass_count, SUM(weight) AS payclass_tie_breaker,
        ROW_NUMBER() OVER(PARTITION BY grower, block, section, year
            ORDER BY COUNT(*) DESC, SUM(weight) DESC) AS row_num
    FROM dbo.wbridge_historyBasies
    GROUP BY grower, block, section, year, payclass
) AS top_payclasses ON
    top_payclasses.row_num = 1 AND
    top_payclasses.grower = whB.grower AND
    top_payclasses.block = whB.block AND
    top_payclasses.section = whB.section AND
    top_payclasses.year = whB.year
3

Thank you Scott.