Most frequently used text in column

  • gideon.e

    SSC Enthusiast

    Points: 190

    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](50) NULL,

    [block] [varchar](50) NULL,

    [section] [varchar](50) NULL,

    [year] [int] NULL,

    [weight] [decimal](5, 2) NULL,

    [payclass] [varchar](50) NULL,

    [yearclass] [varchar](50) 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  require is in the attached Excel file.

    Regards and thank you in advance.

    Attachments:
    You must be logged in to view attached files.
  • gideon.e

    SSC Enthusiast

    Points: 190

    I am also attaching a script file for both the schema and data.

  • John Mitchell-245523

    SSC Guru

    Points: 148372

    Try this.  There may be a slightly more elegant solution, but this should work.  I haven't checked that the results are correct, because I'm wary about opening spreadsheets posted online.

    WITH payclassCounts AS (
    SELECT
    grower
    , block
    , section
    , year
    , payclass
    , yearclass
    , COUNT(*) OVER (PARTITION BY grower, block, section, year, payclass) AS payclassCount
    FROM wbridge_historyBasies
    )
    , OrderedRows AS (
    SELECT
    grower
    , block
    , section
    , year
    , payclass
    , yearclass
    , FIRST_VALUE(payclass) OVER (PARTITION BY grower, block, section, year ORDER BY payclassCount DESC) AS NewValue
    FROM payclassCounts
    )
    UPDATE OrderedRows
    SET yearclass = NewValue;

    John

    Edit: oops - forgot about the weight tie-breaker requirement!  Des's solution will probably work better.

  • DesNorton

    SSC-Insane

    Points: 22818

    I have not checked this against your spreadseet

    WITH cteBase AS (
    SELECT whb.ID, whb.grower, whb.block, whb.section, whb.year, whb.weight, whb.payclass
    , PayClassCounter = COUNT( * ) OVER ( PARTITION BY whb.grower, whb.block, whb.section, whb.year, whb.payclass )
    , WeightSum = SUM( weight ) OVER ( PARTITION BY whb.grower, whb.block, whb.section, whb.year, whb.payclass )
    FROM dbo.wbridge_historyBasies AS whb
    )
    , cteMaxOrder AS (
    SELECT b.ID, b.grower, b.block, b.section, b.year, b.weight, b.payclass, b.PayClassCounter, b.WeightSum
    , rn = ROW_NUMBER() OVER ( PARTITION BY b.grower, b.block, b.section, b.year
    ORDER BY b.PayClassCounter DESC, b.WeightSum DESC )
    FROM cteBase AS b
    )
    , cteFinal AS (
    SELECT mo.grower, mo.block, mo.section, mo.year, mo.weight, mo.payclass
    --, mo.ID, mo.PayClassCounter, mo.WeightSum, mo.rn
    FROM cteMaxOrder AS mo
    WHERE mo.rn = 1
    )
    UPDATE hb
    SET yearclass = f.payclass
    FROM dbo.wbridge_historyBasies AS hb
    INNER JOIN cteFinal AS f
    ON hb.grower = f.grower
    AND hb.block = f.block
    AND hb.section = f.section
    AND hb.year = f.year;
  • John Mitchell-245523

    SSC Guru

    Points: 148372

    Just needed to add a single line to my previous solution in order to take account of the weight requirement.  Again, I've checked that the query runs, but I haven't compared the results to those in the spreadsheet.

    WITH payclassCounts AS (
    SELECT
    grower
    , block
    , section
    , year
    , payclass
    , yearclass
    , COUNT(*) OVER (PARTITION BY grower, block, section, year, payclass) AS payclassCount
    , SUM(weight) OVER (PARTITION BY grower, block, section, year, payclass) AS Weightperpayclass
    FROM wbridge_historyBasies
    )
    , OrderedRows AS (
    SELECT
    grower
    , block
    , section
    , year
    , payclass
    , yearclass
    , FIRST_VALUE(payclass) OVER (PARTITION BY grower, block, section, year ORDER BY payclassCount DESC, Weightperpayclass DESC) AS NewValue
    FROM payclassCounts
    )
    UPDATE OrderedRows
    SET yearclass = NewValue;

    John

  • gideon.e

    SSC Enthusiast

    Points: 190

    Thank you both Des and John. Both queries works. And I must say both are very impressive. A newbie like me would never have gotten it. Thank you once again.

  • John Mitchell-245523

    SSC Guru

    Points: 148372

    You're welcome.  Do you understand how they work?  Make sure you don't put them into production until you do, because you'll be responsible for their operation and maintenance.

    John

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply